Measuring Exposure to the Credit Shock
Description of Firm-Level Data in Amadeus
All the firm-level information we are exploiting comes from Amadeus. The database is one of the products in Bureau van Dijk’s Osiris – together with Bankscope, Isis and Orbis – and provides comprehensive information for approximately 19 million companies in both Western and Eastern Europe. It is not a historical database as it strives for recent information. Financial data from companies within Amadeus are retained fro a rolling period of 8 years. When a new year of data is added, the oldest set of data is dropped, meaning only the most recent data for each company is available. Banks and financial companies are generally excluded from the database, such that the reminder is only commercial companies. Companies are generally excluded from the database, such that the reminder is only commercial companies. Companies are defined in different categories by their operating revenue, total assets, and number of employees: small, medium, large and very large, handled in separate tables of the database. In addition to financial data, the database provides information cross-sectional information about the bankers the company has relationships with. Those bank relationships are crucial for the measure of the credit shock we are studying, proxied by the county-level Commerbank dependence. To cite these data, we can use the following format: Wharton Research Data Services. “WRDS” wrds.wharton.upenn.edu, accessed X.
From Kalemli-Ozcan et al. (2015) we understand some ex ante limitations of the Amadeus database. It will delete a company from the database if the company did not report anything in the last 5 years, contrary to Orbis that keeps the company as long as it is active in the business register. It would lead to a survivorship bias in our underlying firm-level data, meaning that we can achieve a better representative sample combining both Amadeus and Orbis. There is a reporting lag of two years, on average, and there are differences in the coverage of particular variables depending on when the BvD product has been released. Hence, for the 2010 vintage, the company may not have the 2010 filings but the 2010 filings would appear in the 2012 vintage. For our purpose, it means that we might look into financials until 2008 when referring to 2006 financials. Moreover, BvD’s data collection improves over time and hence this lag might vary by country and data vintage.
Additionally, it can be possible that there are multiple financial records for a single closing data due to double reporting of both consolidated and unconsolidated financial variables. There is no perfect solution in using only consolidated or unconsolidated accounts. However, it is possible to identify the consolidation type from the variables repbas in the financials table, and consol in the company information, which can be:
C1: only consolidated accounts,U1: only unconsolidated accounts,C2: both consolidated and unconsolidated, and those coded as C2 are the consolidated ones,U2: both consolidated and unconsolidated, and those coded as U2 are the unconsolidated ones,LF: limited financial information.
After talking with Kilian Huber, we understand that the original data on bank relationships in his paper from Creditreform are not available anymore for research purposes, so we have no other option than to use Amadeus data. The plan is to check later whether our measure is highly correlated to his measure, both at firm- and county-level, or good enough to use it as a proxy of the correct measure of Commerzbank dependence. The same procedure applies in Berg et al. (2021), where they analyse spillovers effects in corporate finance using Amadeus bank relationships and financial data at 2007. It relies on the assumption of long-term stickiness of bank relationships, well documented in Chodorow-Reich (2014) as in Kilian’s and Tobias’ papers.
Data Extraction using the Wharton Research Data Services (WRDS)
Our connection to the Amadeus database is provided by the Wharton Research Data Services, via our universities’ subscription. We need to set up the handshaking to the database in R using PostgreSQL driver. In the older version of my code in Stata, we were using the ODBC driver straight into Stata.
# this is initializes the database connection with the WRDS,
# it can also go in the project's .Rprofile config.
wrds <- RPostgres::dbConnect(Postgres(),
host = "wrds-pgdata.wharton.upenn.edu",
port = 9737,
dbname = "wrds",
sslmode = "require",
user = "alepizzigolotto")For the data extraction we are interested in three tables of the database, which are unique tables for each firm size category.
- amadeus_x table contains information on company profiles. Among all the variables available, we extract the ID
idnrand the namenameof the company, thezipcodeof the location of the factory, together with theaddress,region– which indicates the landkreis – andcity, the incorporation date and yeardateinc, the legal statuslstatusand when the status has changed, the NACE, NAICS and SIC primary codes (nace_prim_code,naics_core_codeandussic_core_code). We retrieve the firm size flagftypefrom the table which we interrogate in the database. - financials_x table contains the unbalanced panel of financial data for each firm kept in the sample at the time of interrogation. We are using those information to understand more about the firm balance sheets, grouping at county level and by firm size flag, using some of those information to weight the county-level Commerzbank dependence at firm level. We retrieve the number of employees
empl, total assetstoas, current loansloan, long-term debtltdb, cost of employeesstaf- from which we can obtain the average wage -, shareholders funds and liabilitiestshf(right-hand side of balance sheet), shareholders fundsshfd(equity), liquidity ratioliqr, solvency ratiosolr, current assetscuas, current liabilitiesculi, working capitalwkca, and operating revenueopre(and more in the database). - bankers_x table contains cross-sectional information about each firm’s bank relationships, where the only information available is the name of the bank
bnk_name, from which we can identify which relationships are with Commerzbank and which are not. - (optional) subsidiaries_x table contains among all companies in Amadeus the information relative subsidiaries within the database, with their name
subs_name, their id numbersubs_bvdepnr, the citysubs_city, statussubs_status, date of informationsubs_date, the total assetssubs_toasand number of employeessubs_empl, and the closing datesubs_clos.
With our function getAmadeusData(db, table, vars, type, cond) we can retrieve data from the Amadeus database via the db pre-initialized connection object to WRDS PosgreSQL server, the selected table table, the firm size flag type and some conditions for the SQL query as arguments. We collect all data for all firms from Germany that are in Amadeus in a list with the following script.
🛑 FIXME put this part of the code and the entire script in a separate script for the pipeline!
# same condition for everybody and then we merge later using the
# incorporation year to filter only the firms active before the 2007
cond <- "WHERE country = 'GERMANY'"
# firm types in Amadeus
firm_types <- c("v", "l", "m", "s")
# company info, financials, bankers
tables <- c("amadeus", "financials", "bankers", "subsidiaries")
vars <- list(company_vars, financials_vars, bankers_vars, subsidiaries_vars)
amadeus_data <- list()
for (t in 1:length(tables)) {
amadeus_data[[t]] <- list()
for (s in 1:length(firm_types)) {
amadeus_data[[t]][[s]] <- getAmadeusData(
wrds, tables[[t]], vars[[t]], firm_types[[s]], cond)
}
amadeus_data[[t]] <- rbindlist(amadeus_data[[t]])
}We save the data in four separate .fst files to be able to reuse them. I have disabled the code here because I have already exported all data from Amadeus in the separate files to work locally without connecting to the database. You can find the last update in the README.md file in the ROOT/data/firms directory.
The raw data consists of a total of 1516363 unique companies of which we have 8176154 unbalanced financial entries, from virtually 1952 to 2022, and 1263901 cross-sectional bank relationships.
Each Firm Belongs to His County: Matching Procedure
The company profile data in Amadeus provide us with different information to perform the matching with the county of their location. In order of relevance, the first information we get is the ZIP code, then we have their region, their city, and eventually their address. This order is given by the quality of the information available, as information can be missing, not updated, or misleading, whereas the ZIP code is pretty stable.
| idnr | name_nat | zipcode | address_nat | region_nat | city_nat |
|---|---|---|---|---|---|
| DE7050297176 | AGENNIX AG | 69120 | IM NEUENHEIMER FELD 515 | Heidelberg | HEIDELBERG |
| DE2010000074 | PSI SOFTWARE AG | 10178 | DIRCKSENSTR. 42-44 | Berlin | BERLIN |
| DE2010000230 | BERGMANN & FRANZ NACHF. GMBH & CO. KG | 10785 | LÜTZOWSTR. 74-76 | Berlin | BERLIN |
| DE2010000474 | LAEGER GMBH | 12347 | TEMPELHOFER WEG 50 | Berlin | BERLIN |
| DE2010000478 | KIEBACK & PETER GMBH & CO. KG | 12347 | TEMPELHOFER WEG 50 | Berlin | BERLIN |
| DE2010000524 | PELIKAN GROUP GMBH | 13507 | AM BORSIGTURM 100 | Havelland | BERLIN |
| DE2010000564 | OTIS GMBH & CO. OHG | 13507 | OTISSTR. 33 | Berlin | BERLIN |
| DE2010000581 | SIEMENS AKTIENGESELLSCHAFT | 13629 | NONNENDAMMALLEE 101-104 | Berlin | BERLIN |
| DE2010000805 | DEGEWO AKTIENGESELLSCHAFT | 10785 | POTSDAMER STR. 60 | Berlin | BERLIN |
| DE2010001043 | SPIELE MAX GMBH | 12249 | HAYNAUER STR. 72 A | Postdam-Mittelmark | BERLIN |
ZIP Codes – County Name – Village Name – County Codes Matching Table
We use data from the Postleitzahlen Deutschland, which provides an accurate matching table between ZIP codes and KKZ codes, the county identifiers that are also used in the German Socio-Economic Panel. From these data, we obtain for each ZIP code plz the corresponding county code ags, the Landkreis name landkreis (if it is a landkreis), the Bundesland name and the location name ort. We keep KKZ at county level (five digits).
# load from website the .csv file with the matches
kreise_codes <- read.csv(
url(glue::glue(
"https://www.suche-postleitzahl.org/",
"download_files/public/zuordnung_plz_ort_landkreis.csv"))
) %>%
as.data.table()
# keep KKZ codes at county level in five digits
kreise_codes[, kkz := str_sub(
fifelse(str_length(ags) == 7, paste0("0", ags), as.character(ags)), 1, 5)]
# in the .do file we put Landkreis and Kreis at the end of the string
# we do it here as well and then let's see
kreise_codes[, names := fifelse(
str_trim(landkreis) == "", paste0(ort, " Stadt"),
str_replace(landkreis,"(Landkreis|Kreis|Eifelkreis)\\s(.+)", "\\2 \\1"))]| idnr | name_nat | zipcode | address_nat | region_nat | city_nat |
|---|---|---|---|---|---|
| DE7050297176 | AGENNIX AG | 69120 | IM NEUENHEIMER FELD 515 | Heidelberg | HEIDELBERG |
| DE2010000074 | PSI SOFTWARE AG | 10178 | DIRCKSENSTR. 42-44 | Berlin | BERLIN |
| DE2010000230 | BERGMANN & FRANZ NACHF. GMBH & CO. KG | 10785 | LÜTZOWSTR. 74-76 | Berlin | BERLIN |
| DE2010000474 | LAEGER GMBH | 12347 | TEMPELHOFER WEG 50 | Berlin | BERLIN |
| DE2010000478 | KIEBACK & PETER GMBH & CO. KG | 12347 | TEMPELHOFER WEG 50 | Berlin | BERLIN |
| DE2010000524 | PELIKAN GROUP GMBH | 13507 | AM BORSIGTURM 100 | Havelland | BERLIN |
| DE2010000564 | OTIS GMBH & CO. OHG | 13507 | OTISSTR. 33 | Berlin | BERLIN |
| DE2010000581 | SIEMENS AKTIENGESELLSCHAFT | 13629 | NONNENDAMMALLEE 101-104 | Berlin | BERLIN |
| DE2010000805 | DEGEWO AKTIENGESELLSCHAFT | 10785 | POTSDAMER STR. 60 | Berlin | BERLIN |
| DE2010001043 | SPIELE MAX GMBH | 12249 | HAYNAUER STR. 72 A | Postdam-Mittelmark | BERLIN |
First Step: ZIP Code Matching
First, we try to match all companies with their county code using the ZIP codes indicated in the Amadeus database.
# matching table zip-kkz keeping unique values in the previous data
match_zips <- unique(kreise_codes[, .(plz, kkz)])
# we keep zip code as integer
companies[, zipcode := as.integer(zipcode)]
# double check that Landkreis and Kreis are at the end of the
# A[B, on = 'a', bb := i.b], join A with B and upddate A by reference
companies[match_zips, on = .(zipcode = plz), kkz := i.kkz]Second Step: String Fuzzy Matching with County Names
After matching ZIP codes with the information in the Amadeus’ companies, we are left with 26994 unmatched firms. For most of them (26269) we have information of the region where those firms are located. We can use UTF-8 original names for the match, contrary to Stata that messes up all the time with strings. After some adjustments of the string names, we run a Jaro-Winkler distance algorithm to fuzzy matching the region name of firms without a match with the ZIP code (or missing ZIP code) with the region names in the matching table, specified in Landkreis for most of all the rural areas and Stadt for most of all the urban areas. We select for each unmatched companies’ region name the county name with the smallest distance in the matching table. The Jaro-Winkler distance is not perfect, so we need to perform some manual changes to fit the names after the fuzzy matching procedure.
# reverse epithets of counties just in case
companies[, region_nat := str_replace(
region_nat,"(Landkreis|Kreis|Eifelkreis)\\s(.+)", "\\2 \\1")]
# select from the matching table only names and county codes
match_names <- unique(kreise_codes[, .(kkz, names)])
# select only the region names for unmatched firms in the step before
unmatched_regions <- data.table(
names = unique(companies[is.na(kkz) & !is.na(region_nat), region_nat]))
# create grid for matching each region name with names in the matching table
scores <- expand.grid(unmatched_regions[, names], match_names[, names])
# run Jaro-Winkler string distance function
scores$dist <- stringdist::stringdist(scores$Var1, scores$Var2, method = "jw")
colnames(scores) <- c("region_nat", "names", "dist")
# there are some region names that are Bundesland names and we remove it
states <- unique(kreise_codes[bundesland != "Berlin", bundesland])
# select the name with the smallest distance for each region name
match_names <- scores %>%
dplyr::filter(!(region_nat %in% states)) %>%
dplyr::group_by(region_nat) %>%
dplyr::arrange(dist) %>%
dplyr::slice(1) %>%
dplyr::select(region_nat, names) %>%
as.data.table()
# add county codes back from the matching table
match_names[kreise_codes, on = .(names), kkz := i.kkz]
# manual corrections
match_names[region_nat %like% "München Landeshaupt", names := "München Stadt"]
match_names[region_nat %like% "Lauenburg", names := "Herzogtum Lauenburg Kreis"]
match_names[region_nat %like% "Neuss", names := "Rhein-Neuss Kreis"]
match_names[region_nat %like% "Wendel", names := "St. Wendel Landkreis"]
# add county codes to the unmatched companies
companies[
match_names, on = .(region_nat), kkz := fifelse(is.na(kkz), i.kkz, kkz)]Third Step: String Fuzzy Matching with “City” Names
After using strings with region names for the match of companies with their county code, we still have 1107 unmatched firms. Of those firms, 925 have information about the city where the firm is located. First, we search for perfect match of of companies’ city names that are unique at county level in the matching table for Germany as a whole. Second, we use the Jaro-Winkler distance again combining information on the city with the Bundesland in the matching table to assign county codes for the remaining cities.
# select the city of firms that are left unmatched
unmatched_cities <- unique(
companies[is.na(kkz) & !is.na(city_nat), c("city_nat", "region_nat")])
# select cities by couinty codes and their Bundesland
match_cities <- unique(kreise_codes[, .(kkz, ort, bundesland)])
# count towns with the same name for an indicator
match_cities[, n_cities := .N, by = .(ort)][, city_nat := toupper(ort)]
# exact match cities in the unmatched firms, when cities has unique name
unmatched_cities[
match_cities[n_cities == 1, .(kkz, city_nat)],
on = .(city_nat), kkz := i.kkz]
# fuzzy matching for remaining cities using bundesland names
match_cities[, names := paste0(city_nat, " - ", toupper(bundesland))]
scores <- expand.grid(
unique(unmatched_cities[is.na(kkz), city_nat]), match_cities[, names])
scores$dist <- stringdist::stringdist(scores$Var1, scores$Var2, method = "jw")
colnames(scores) <- c("city_nat", "names", "dist")
scores <- scores %>%
dplyr::group_by(city_nat) %>%
dplyr::arrange(dist) %>%
dplyr::slice(1) %>%
dplyr::select(city_nat, names) %>%
as.data.table()
# manual correction of bad matching
nuisance_cities <- c("SENSBACHTAL", "RIEZLERN", "HIRSCHEGG", "TWIEFLINGEN",
"HOYERSHAUSEN", "ZÖBLITZ", "WIES", "NEUNKIRCHEN",
"HELSINKI", "SACHSEN", "KUOPIO", "TAMPERE")
scores <- scores[!(city_nat %in% nuisance_cities)]
# manual correction city mismatch
city_mismatch <- c("LICHTE", "HADMERSLEBEN", "BRANDENBURG", "MARL", "VOERDE",
"FREIBURG", "LUDWIGSHAFEN", "ST. WENDEL", "MUNICH")
city_correction <- c("Sonneberg - Thüringen", "Oschersleben - Sachsen-Anhalt",
"Brandenburg an der Havel - Brandenburg",
"Marl - Nordrhein-Westfalen",
"Voerde (Niederrhein) - Nordrhein-Westfalen",
"Freiburg im Breisgau - Baden-Württemberg",
"Ludwigshafen am Rhein - Rheinland-Pfalz",
"Sankt Wendel - Saarland", "München - Bayern")
for (s in 1:length(city_mismatch)) {
scores[city_nat == city_mismatch[s], names := toupper(city_correction[s])]
}
scores <- merge(scores, match_cities[, .(names, kkz)], by = "names")
unmatched_cities[
scores[, .(kkz, city_nat)], on = .(city_nat),
kkz := fifelse(is.na(kkz), i.kkz, kkz)]
unmatched_cities <- unique(unmatched_cities[!is.na(kkz), .(kkz, city_nat)])
# add county codes to the unmatched companies
companies[unmatched_cities, on = .(city_nat),
kkz := fifelse(is.na(kkz), i.kkz, kkz)]Unfortunately, we have to manually correct some of the cities and, after merging back to the companies table, we have been able to match all but 227 firms, with a total of 1516136 firms in the sample.
Fourth-Step: Harmonize county codes with the official German Socio-Economic Panel Recoded County Codes.
The last operation to do is to match the county codes we have matched in the Amadeus sample with the county level codes contained in the German Socio-Economic Panel. In fact, the SOEP with county identifiers we access in the SOEPRemote has re-coded county codes keeping track of counties that have been merged at different points in time between 2000 and 2016, but never the inverse process. We have retrieved a table from the internal server at DIW Berlin that contains the matches between KKZ and KKZ_REK, namely the standard codes and the re-coded ones.
We convert the county codes from string to numeric (as some of them they keep a zero in the front, which is not the case for the SOEPRemote codes), and we perform the match using the integer codes.
Firm Selection and Bank Relationships
After matching the universe of firms to their county in the Amadeus database, we subset for those firms that were established before 2007 for which we have information of bank relationships. This is our way to identify firms active on the eve of the credit shock happening in 2008Q3, and the year at which Huber (2018) collects the bank relationships. After filtering, we count the number of bank relationships for each firm, and how many of those relationships are with Commerzbank using string matching on the name, and we assign back the values to our firms. The row bankers table looks as the following before subsetting information for the firms we are interested of.
| bnk_name | compcat | country | idnr | ftype |
|---|---|---|---|---|
| Commerzbank AG | VERY LARGE | GERMANY | DE2010000564 | V |
| Berliner Sparkasse Ndl. der Landesbank Berlin AG | VERY LARGE | GERMANY | DE2010000074 | V |
| Commerzbank AG | VERY LARGE | GERMANY | DE2010000074 | V |
| Deutsche Postbank AG | VERY LARGE | GERMANY | DE2010000074 | V |
| HypoVereinsbank UniCredit Bank AG | VERY LARGE | GERMANY | DE2010000074 | V |
| Commerzbank AG | VERY LARGE | GERMANY | DE2010000230 | V |
| Deutsche Postbank AG | VERY LARGE | GERMANY | DE2010000230 | V |
| Berliner Bank NL der Dt. Bank Priv.-u.Geschäftsk. AG | VERY LARGE | GERMANY | DE2010000474 | V |
| Commerzbank AG | VERY LARGE | GERMANY | DE2010000474 | V |
| Berliner Bank NL der Dt. Bank Priv.-u.Geschäftsk. AG | VERY LARGE | GERMANY | DE2010000478 | V |
Filtering firms established before 2007 leaves us with a total of 631933 firms. Merging the bank relationships with this sub-sample of firms makes us losing 78477 firms, of which we have no information connected abbout the bank accounts, remaining with a total of 553456. We obtain a total of , of which are with Commerzbank (10.08%). We use the strings in bnk_name to identify Commerzbank. 84.74% of firms have no accounts with Commerzbank (which means that around 15% of the firms have a relationship with Commerzbank, roughly 10 percentage points less than the relationships in Huber’s paper). This is the first step for the creation of the measure of Commerzbank dependence, and the baseline for the firm-level measure.
The following table summarizes for the firm sample the distribution of bank relationships across different firm size flags and the number of firms per county in each category. It is interesting to see that the largest part of our firm sample is dominated by small and medium firms with few bank relationships and fewer with Commerzbank. The median number of bank relationships is one or two, independently from the firm size flag. The histograms give the flavour of the distribution of the frequency of firms across firm type categories and in total.
| Variable | Firm Size | Overall, N = 553456 | |||
|---|---|---|---|---|---|
| Large, N = 25996 | Medium, N = 166440 | Small, N = 353747 | Very Large, N = 7273 | ||
| No. of Bank Relationships | |||||
| N missing (% missing) | 0 (0) | 0 (0) | 0 (0) | 0 (0) | 0 (0) |
| Mean | 1.996 | 1.696 | 1.377 | 2.305 | 1.514 |
| SD | 1.121 | 0.837 | 0.641 | 1.452 | 0.775 |
| Minimum | 1 | 1 | 1 | 1 | 1 |
| Median (IQR) | 2 (1 - 3) | 2 (1 - 2) | 1 (1 - 2) | 2 (1 - 3) | 1 (1 - 2) |
| Maximum | 15 | 11 | 11 | 15 | 15 |
| No. of Relationships with Commerzbank | |||||
| N missing (% missing) | 0 (0) | 0 (0) | 0 (0) | 0 (0) | 0 (0) |
| Mean | 0.28 | 0.174 | 0.128 | 0.423 | 0.153 |
| SD | 0.449 | 0.379 | 0.334 | 0.498 | 0.36 |
| Minimum | 0 | 0 | 0 | 0 | 0 |
| Median (IQR) | 0 (0 - 1) | 0 (0 - 0) | 0 (0 - 0) | 0 (0 - 1) | 0 (0 - 0) |
| Maximum | 2 | 2 | 2 | 4 | 4 |
| Number of Firms per County | |||||
| N missing (% missing) | 0 (0) | 0 (0) | 0 (0) | 0 (0) | 0 (0) |
| Mean | 64.99 | 416.1 | 884.367 | 18.183 | 345.91 |
| SD | 95.602 | 484.803 | 1276.452 | 34.667 | 766.922 |
| Minimum | 0 | 0 | 2 | 0 | 0 |
| Median (IQR) | 42 (27.75 - 71.25) | 299 (192.75 - 489.25) | 575 (376 - 952.75) | 9.5 (5 - 18) | 129.5 (23 - 416) |
| Maximum | 1104 | 6202 | 16568 | 377 | 16568 |
Figure 1: Histogram of the share of total number of firms per county by size category (top) and in total (bottom).
Using the bank relationships from Amadeus, we construct a simple measure of firm-level Commerzbank dependence exactly following Huber (2018). We obtain a slightly different distribution compared to the figure in his paper. It is probably because we are considering way more firms compared to his, with lower number of relationship banks per firm and more firms without any relationship with Commerzbank. It might be related to a higher presence of small and medium firms.
Figure 2: Histogram of the fraction of firms in the sample at a certain number of bank relationships in total and only with Commerzbank.
Figure 3: Distribution of firm-level Commerzbank dependence conditional and unconditional on having at least one bank relationship with Commerzbank in the sample.
Figure 4: Histogram of firm-level Commerzbank dependence as calculated in Huber (2018) from his firm sample.
In the initial version of the draft for this project, we followed a similar structure for retrieving the firm data, matching them with their county codes and subset the firms established before 2007 and of which we have the bank relationships. The main difference was that we were matching after the first step (ZIP codes) with a more manual procedure using regional names. From that time - around June-July 2020 - some of the firms or bank relationships have been dropped from the database, and we did not save the full information of those firms but just the bank relationships, the identifiers and the four-digits industry code. We can compare the distribution of the firm-level Commerzbank dependence of those firms with the distribution in this sample we have recently retrieved.
In the past firm sample, we have 70827 additional firms. It seems to be caused by a combination of firms that were dropped in the whole Amadeus database and updated to missing information in the bankers table. Trying to find the firms we previously retrieved in this sample leaves us with only 549754. When it comes to the firm-level Commerzbank dependence distribution from the previous draft, it is very similar to the one we obtain with the current firms.
Figure 5: Distribution of firm-level Commerzbank dependence conditional and unconditional on having at least one bank relationship with Commerzbank, using the firm data from the previous draft with the bankers we retrieved at that time (Stata code).
Firm-Level Weights for County Level Exposure
In this section, we go through the weighting procedure for calculating the county-level Commerzbank dependence. Ideally, we want to take care of all the within-county heterogeneity among firms that cannot be controlled by county-level fixed effects when applying our main identification. Firms are clustered over two main dimensions when considering within-county variation, which are the firm size, divided in four categories, and industry sector. From previous section, firms with different size category do not present striking differences in the number of bank relationships and the number of those with Commerzbank, but can have different sensibility to a general credit shock in terms of liquidity availability. Moreover, we do not exactly know what means to be a firm of size X. On the other hand, firms within the same industry can present similar characteristics, especially when they are within the same regional boundaries.
Two-Digits Industry Sectors in the Firm Sample
From the companies profile, for each firm we have information of the four digits NACE v2 industry code. We group firms using the two-digit specification of industry sector to see the number of firms in the sample after restricting by incorporation date and presence of bank relationships for each group. There are 18 firms for which the industry code is not available. Industry composition within a county should be taken care by the county-level fixed effects in our identification.
| Two-Digits NACE Codes | Industry Description | Large | Medium | Small | Very Large | Mean | Median | Max. Value | Mean | Median | Max. Value | Mean Share per County (%) | Total Firms |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 70 | Activities of head offices; management consultancy activities | 1466 | 4625 | 59026 | 1287 | 1.5 | 1.0 | 12 | 0.183 | 0 | 2 | 11.033 | 66404 |
| 43 | Specialised construction activities | 450 | 16479 | 31996 | 29 | 1.6 | 1.0 | 8 | 0.094 | 0 | 2 | 9.782 | 48954 |
| 46 | Wholesale trade, except of motor vehicles and motorcycles | 2508 | 16914 | 28475 | 893 | 1.7 | 1.0 | 11 | 0.215 | 0 | 2 | 8.435 | 48790 |
| 68 | Real estate activities | 2998 | 8946 | 29306 | 451 | 1.4 | 1.0 | 15 | 0.148 | 0 | 1 | 6.716 | 41701 |
| 47 | Retail trade, except of motor vehicles and motorcycles | 806 | 11026 | 29296 | 217 | 1.5 | 1.0 | 10 | 0.108 | 0 | 4 | 8.237 | 41345 |
| 45 | Wholesale and retail trade and repair of motor vehicles and motorcycles | 652 | 6744 | 10989 | 177 | 1.6 | 1.0 | 10 | 0.104 | 0 | 1 | 3.993 | 18562 |
| 71 | Architectural and engineering activities; technical testing and analysis | 468 | 5208 | 10592 | 80 | 1.4 | 1.0 | 8 | 0.165 | 0 | 1 | 2.843 | 16348 |
| 25 | Manufacture of fabricated metal products, except machinery and equipment | 708 | 7785 | 6651 | 112 | 1.8 | 2.0 | 15 | 0.178 | 0 | 1 | 3.078 | 15256 |
| 62 | Computer programming, consultancy and related activities | 557 | 4164 | 9606 | 129 | 1.3 | 1.0 | 6 | 0.172 | 0 | 1 | 2.092 | 14456 |
| 41 | Construction of buildings | 580 | 5156 | 7161 | 81 | 1.5 | 1.0 | 15 | 0.088 | 0 | 1 | 2.716 | 12978 |
| 82 | Office administrative, office support and other business support activities | 645 | 3217 | 8711 | 163 | 1.4 | 1.0 | 8 | 0.176 | 0 | 1 | 2.016 | 12736 |
| 94 | Activities of membership organisations | 298 | 1962 | 10128 | 46 | 1.3 | 1.0 | 6 | 0.083 | 0 | 1 | 1.836 | 12434 |
| 93 | Sports activities and amusement and recreation activities | 134 | 1521 | 7979 | 20 | 1.3 | 1.0 | 9 | 0.052 | 0 | 1 | 1.856 | 9654 |
| 66 | Activities auxiliary to financial services and insurance activities | 220 | 1163 | 8180 | 87 | 1.4 | 1.0 | 6 | 0.177 | 0 | 1 | 1.592 | 9650 |
| 28 | Manufacture of machinery and equipment n.e.c. | 912 | 4621 | 3195 | 234 | 1.9 | 2.0 | 9 | 0.276 | 0 | 2 | 1.752 | 8962 |
| 49 | Land transport and transport via pipelines | 444 | 4277 | 3930 | 60 | 1.5 | 1.0 | 8 | 0.108 | 0 | 1 | 1.845 | 8711 |
| 64 | Financial service activities, except insurance and pension funding | 659 | 1320 | 6202 | 429 | 1.5 | 1.0 | 10 | 0.207 | 0 | 3 | 1.304 | 8610 |
| 96 | Other personal service activities | 312 | 2186 | 5773 | 49 | 1.3 | 1.0 | 6 | 0.125 | 0 | 2 | 1.431 | 8320 |
| 69 | Legal and accounting activities | 98 | 2597 | 4791 | 24 | 1.7 | 1.0 | 11 | 0.190 | 0 | 1 | 1.141 | 7510 |
| 52 | Warehousing and support activities for transportation | 542 | 3101 | 3094 | 133 | 1.6 | 1.0 | 8 | 0.216 | 0 | 1 | 1.196 | 6870 |
| 74 | Other professional, scientific and technical activities | 218 | 1152 | 5029 | 84 | 1.4 | 1.0 | 7 | 0.171 | 0 | 1 | 0.984 | 6483 |
| 81 | Services to buildings and landscape activities | 681 | 2717 | 2926 | 81 | 1.5 | 1.0 | 7 | 0.131 | 0 | 1 | 1.125 | 6405 |
| 88 | Social work activities without accommodation | 957 | 2660 | 2153 | 129 | 1.4 | 1.0 | 7 | 0.058 | 0 | 1 | 1.060 | 5899 |
| 32 | Other manufacturing | 196 | 1925 | 3301 | 41 | 1.5 | 1.0 | 6 | 0.156 | 0 | 2 | 1.062 | 5463 |
| 73 | Advertising and market research | 104 | 1219 | 3971 | 19 | 1.4 | 1.0 | 5 | 0.181 | 0 | 1 | 0.720 | 5313 |
| 56 | Food and beverage service activities | 169 | 2316 | 2795 | 14 | 1.2 | 1.0 | 5 | 0.101 | 0 | 2 | 0.874 | 5294 |
| 85 | Education | 274 | 1811 | 2834 | 15 | 1.3 | 1.0 | 9 | 0.108 | 0 | 1 | 0.834 | 4934 |
| 10 | Manufacture of food products | 597 | 2388 | 1731 | 159 | 1.6 | 1.0 | 8 | 0.118 | 0 | 1 | 1.077 | 4875 |
| 01 | Crop and animal production, hunting and related service activities | 158 | 2501 | 2203 | 11 | 1.4 | 1.0 | 6 | 0.066 | 0 | 1 | 1.520 | 4873 |
| 26 | Manufacture of computer, electronic and optical products | 429 | 2101 | 2023 | 116 | 1.7 | 1.0 | 10 | 0.262 | 0 | 1 | 0.890 | 4669 |
| 55 | Accommodation | 148 | 2106 | 2114 | 13 | 1.4 | 1.0 | 6 | 0.089 | 0 | 1 | 0.892 | 4381 |
| 77 | Rental and leasing activities | 130 | 1180 | 2865 | 24 | 1.5 | 1.0 | 6 | 0.147 | 0 | 1 | 0.803 | 4199 |
| 35 | Electricity, gas, steam and air conditioning supply | 628 | 1149 | 2061 | 283 | 1.5 | 1.0 | 7 | 0.157 | 0 | 1 | 0.973 | 4121 |
| 86 | Human health activities | 754 | 1580 | 1342 | 333 | 1.5 | 1.0 | 6 | 0.101 | 0 | 1 | 0.762 | 4009 |
| 18 | Printing and reproduction of recorded media | 111 | 1244 | 2584 | 15 | 1.8 | 2.0 | 7 | 0.149 | 0 | 1 | 0.684 | 3954 |
| 22 | Manufacture of rubber and plastic products | 442 | 2075 | 1286 | 73 | 1.9 | 2.0 | 6 | 0.261 | 0 | 1 | 0.831 | 3876 |
| 87 | Residential care activities | 803 | 2212 | 704 | 111 | 1.5 | 1.0 | 13 | 0.064 | 0 | 1 | 0.795 | 3830 |
| 16 | Manufacture of wood and of products of wood and cork, except furniture; manufacture of articles of straw and plaiting materials | 104 | 1482 | 2019 | 17 | 1.7 | 2.0 | 9 | 0.106 | 0 | 1 | 0.849 | 3622 |
| 23 | Manufacture of other non-metallic mineral products | 210 | 1398 | 1672 | 49 | 1.8 | 2.0 | 8 | 0.178 | 0 | 1 | 0.778 | 3329 |
| 42 | Civil engineering | 177 | 1934 | 1094 | 24 | 1.6 | 1.0 | 8 | 0.133 | 0 | 1 | 0.740 | 3229 |
| 79 | Travel agency, tour operator and other reservation service and related activities | 52 | 662 | 2428 | 12 | 1.4 | 1.0 | 5 | 0.158 | 0 | 2 | 0.541 | 3154 |
| 27 | Manufacture of electrical equipment | 287 | 1439 | 1173 | 98 | 1.8 | 2.0 | 6 | 0.254 | 0 | 1 | 0.584 | 2997 |
| 38 | Waste collection, treatment and disposal activities; materials recovery | 234 | 1270 | 1135 | 36 | 1.6 | 1.0 | 7 | 0.177 | 0 | 1 | 0.604 | 2675 |
| 58 | Publishing activities | 129 | 710 | 1655 | 30 | 1.7 | 1.0 | 8 | 0.193 | 0 | 1 | 0.411 | 2524 |
| 33 | Repair and installation of machinery and equipment | 64 | 856 | 1259 | 12 | 1.6 | 1.0 | 7 | 0.158 | 0 | 1 | 0.469 | 2191 |
| 78 | Employment activities | 341 | 1012 | 744 | 46 | 1.4 | 1.0 | 6 | 0.223 | 0 | 3 | 0.407 | 2143 |
| 59 | Motion picture, video and television programme production, sound recording and music publishing activities | 44 | 463 | 1528 | 13 | 1.3 | 1.0 | 5 | 0.209 | 0 | 1 | 0.285 | 2048 |
| 72 | Scientific research and development | 163 | 703 | 1085 | 57 | 1.3 | 1.0 | 6 | 0.188 | 0 | 1 | 0.396 | 2008 |
| 20 | Manufacture of chemicals and chemical products | 256 | 829 | 673 | 124 | 1.9 | 2.0 | 8 | 0.340 | 0 | 1 | 0.388 | 1882 |
| 31 | Manufacture of furniture | 82 | 754 | 834 | 12 | 1.7 | 2.0 | 7 | 0.136 | 0 | 1 | 0.378 | 1682 |
| 92 | Gambling and betting activities | 27 | 395 | 900 | 11 | 1.2 | 1.0 | 6 | 0.134 | 0 | 1 | 0.295 | 1333 |
| 24 | Manufacture of basic metals | 179 | 687 | 382 | 64 | 1.9 | 2.0 | 8 | 0.300 | 0 | 1 | 0.316 | 1312 |
| 13 | Manufacture of textiles | 94 | 577 | 605 | 13 | 1.8 | 2.0 | 6 | 0.239 | 0 | 1 | 0.334 | 1289 |
| 08 | Other mining and quarrying | 58 | 631 | 506 | 5 | 1.7 | 2.0 | 9 | 0.141 | 0 | 1 | 0.367 | 1200 |
| 84 | Public administration and defence; compulsory social security | 114 | 381 | 557 | 15 | 1.4 | 1.0 | 5 | 0.061 | 0 | 1 | 0.281 | 1067 |
| 29 | Manufacture of motor vehicles, trailers and semi-trailers | 116 | 473 | 408 | 66 | 1.8 | 2.0 | 10 | 0.246 | 0 | 2 | 0.272 | 1063 |
| 95 | Repair of computers and personal and household goods | 14 | 204 | 711 | 4 | 1.4 | 1.0 | 5 | 0.133 | 0 | 1 | 0.214 | 933 |
| 11 | Manufacture of beverages | 75 | 457 | 351 | 29 | 2.0 | 2.0 | 5 | 0.134 | 0 | 1 | 0.320 | 912 |
| 17 | Manufacture of paper and paper products | 113 | 458 | 282 | 48 | 2.0 | 2.0 | 6 | 0.314 | 0 | 1 | 0.250 | 901 |
| 50 | Water transport | 47 | 356 | 478 | 16 | 1.3 | 1.0 | 6 | 0.203 | 0 | 1 | 0.426 | 897 |
| 63 | Information service activities | 41 | 228 | 584 | 16 | 1.3 | 1.0 | 5 | 0.203 | 0 | 1 | 0.184 | 869 |
| 90 | Creative, arts and entertainment activities | 66 | 263 | 532 | 3 | 1.3 | 1.0 | 5 | 0.120 | 0 | 1 | 0.195 | 864 |
| 80 | Security and investigation activities | 136 | 341 | 291 | 13 | 1.6 | 1.0 | 6 | 0.207 | 0 | 1 | 0.201 | 781 |
| 14 | Manufacture of wearing apparel | 48 | 276 | 338 | 14 | 1.9 | 2.0 | 5 | 0.235 | 0 | 1 | 0.217 | 676 |
| 30 | Manufacture of other transport equipment | 36 | 256 | 262 | 26 | 1.7 | 1.0 | 6 | 0.228 | 0 | 1 | 0.191 | 580 |
| 21 | Manufacture of basic pharmaceutical products and pharmaceutical preparations | 81 | 214 | 182 | 66 | 1.9 | 2.0 | 8 | 0.330 | 0 | 1 | 0.170 | 543 |
| 61 | Telecommunications | 45 | 154 | 327 | 15 | 1.3 | 1.0 | 4 | 0.226 | 0 | 1 | 0.158 | 541 |
| 53 | Postal and courier activities | 74 | 181 | 243 | 14 | 1.3 | 1.0 | 4 | 0.176 | 0 | 1 | 0.162 | 512 |
| 37 | Sewerage | 27 | 225 | 241 | 6 | 1.5 | 1.0 | 5 | 0.132 | 0 | 1 | 0.172 | 499 |
| 91 | Libraries, archives, museums and other cultural activities | 30 | 133 | 259 | 3 | 1.4 | 1.0 | 6 | 0.120 | 0 | 1 | 0.158 | 425 |
| 60 | Programming and broadcasting activities | 15 | 132 | 189 | 6 | 1.4 | 1.0 | 5 | 0.228 | 0 | 1 | 0.171 | 342 |
| 15 | Manufacture of leather and related products | 20 | 122 | 183 | 7 | 1.9 | 2.0 | 7 | 0.226 | 0 | 1 | 0.197 | 332 |
| 02 | Forestry and logging | 7 | 113 | 209 | 2 | 1.3 | 1.0 | 4 | 0.076 | 0 | 1 | 0.176 | 331 |
| 36 | Water collection, treatment and supply | 86 | 105 | 82 | 13 | 1.7 | 1.0 | 6 | 0.122 | 0 | 1 | 0.149 | 286 |
| 39 | Remediation activities and other waste management services | 7 | 65 | 88 | 1 | 1.5 | 1.0 | 3 | 0.217 | 0 | 1 | 0.117 | 161 |
| 51 | Air transport | 8 | 35 | 95 | 3 | 1.4 | 1.0 | 4 | 0.255 | 0 | 1 | 0.106 | 141 |
| 03 | Fishing and aquaculture | 3 | 27 | 54 | 0 | 1.4 | 1.0 | 3 | 0.095 | 0 | 1 | 0.154 | 84 |
| 75 | Veterinary activities | 3 | 28 | 40 | 0 | 1.3 | 1.0 | 5 | 0.085 | 0 | 1 | 0.090 | 71 |
| 19 | Manufacture of coke and refined petroleum products | 11 | 19 | 18 | 20 | 2.0 | 2.0 | 6 | 0.456 | 0 | 1 | 0.104 | 68 |
| 09 | Mining support service activities | 8 | 22 | 10 | 4 | 1.4 | 1.0 | 3 | 0.227 | 0 | 1 | 0.138 | 44 |
| 12 | Manufacture of tobacco products | 4 | 4 | 4 | 7 | 1.7 | 1.0 | 4 | 0.368 | 0 | 1 | 0.081 | 19 |
| 06 | Extraction of crude petroleum and natural gas | 4 | 4 | 2 | 7 | 1.6 | 1.0 | 3 | 0.471 | 0 | 1 | 0.100 | 17 |
| 97 | Activities of households as employers of domestic personnel | 0 | 6 | 7 | 0 | 1.5 | 1.0 | 3 | 0.000 | 0 | 0 | 0.061 | 13 |
| 07 | Mining of metal ores | 0 | 5 | 1 | 2 | 1.6 | 1.5 | 3 | 0.250 | 0 | 1 | 0.050 | 8 |
| 99 | Activities of extraterritorial organisations and bodies | 0 | 1 | 5 | 0 | 1.7 | 1.5 | 3 | 0.167 | 0 | 1 | 0.105 | 6 |
| 05 | Mining of coal and lignite | 0 | 2 | 0 | 2 | 1.8 | 1.5 | 3 | 0.250 | 0 | 1 | 0.129 | 4 |
| 98 | Undifferentiated goods- and services-producing activities of private households for own use | 0 | 0 | 1 | 0 | 1.0 | 1.0 | 1 | 0.000 | 0 | 0 | 0.040 | 1 |
| 65 | Insurance, reinsurance and pension funding, except compulsory social security | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0 | 0.000 | 0 | 0 | 0.000 | 0 |
A large part of the firms are in management consultancy activities, followed by construction and wholesale trade. On average, heavy industry firms have the most number of bank relationships, whereas the most number of bank relationships with Commerzbank are on wholesale trade, manifacture of machinery and equipment, warehousing, manufacture of electronic products and manufacture of rubber and plastic products. We also report the average industry share at county level. We consider industry shares at county level as frequency weight for the size of an industry within a county.
Exploring the Financial Panel and Firm Size Categories
In this section, we explore the financial variables we fetched from the financials_x tables in Amadeus. In primis, we remove duplicates for the entire financials data where there are both consolidated and unconsolidated data. We summarize the financial variables for the entire sample after filtering duplicates by firm size category, to make some sense of what it means to be a firm of size X in the Amadeus data, how complete are the financial information and which years are more prominent, given the existing survivorship bias.
| Variable | Firm Size | Overall, N = 8138930 | |||
|---|---|---|---|---|---|
| Large, N = 342937 | Medium, N = 2275628 | Small, N = 5429817 | Very Large, N = 90548 | ||
| Closing Date (Year) | |||||
| N missing (% missing) | 0 (0) | 0 (0) | 0 (0) | 0 (0) | 0 (0) |
| Mean | 2015.213 | 2015.279 | 2016.426 | 2015.175 | 2016.04 |
| SD | 3.173 | 3.275 | 3.431 | 3.181 | 3.418 |
| Minimum | 1999 | 1997 | 1952 | 1999 | 1952 |
| Median (IQR) | 2016 (2013 - 2018) | 2016 (2013 - 2018) | 2017 (2014 - 2019) | 2015 (2013 - 2018) | 2017 (2014 - 2019) |
| Maximum | 2022 | 2022 | 2022 | 2022 | 2022 |
| Number of Employees | |||||
| N missing (% missing) | 103687 (0.302) | 1059752 (0.466) | 1391033 (0.256) | 11718 (0.129) | 2566190 (0.315) |
| Mean | 160.869 | 31.06 | 4.158 | 2204.672 | 47.884 |
| SD | 210.244 | 138.557 | 195.517 | 14106.239 | 1707.729 |
| Minimum | 1 | 0 | 0 | 1 | 0 |
| Median (IQR) | 114 (28 - 219) | 21 (12 - 39) | 2 (1 - 5) | 522 (148 - 1327) | 3 (1 - 11) |
| Maximum | 30500 | 90335 | 161000 | 655722 | 655722 |
| Cost of Employees (EUR Mln.) | |||||
| N missing (% missing) | 184970 (0.539) | 2076838 (0.913) | 5368239 (0.989) | 17922 (0.198) | 7647969 (0.94) |
| Mean | 8.688 | 2.308 | 0.596 | 111.285 | 20.267 |
| SD | 61.308 | 2.195 | 14.579 | 826.275 | 321.991 |
| Minimum | -20.427 | -8.806 | -1.239 | -64.913 | -64.913 |
| Median (IQR) | 6.361 (3.185 - 11.05) | 1.75 (0.68 - 3.387) | 0.204 (0.079 - 0.382) | 27.925 (10.015 - 62.945) | 3.033 (0.753 - 8.002) |
| Maximum | 22660 | 75.643 | 1338 | 42914 | 42914 |
| Operating Revenues (EUR Mln.) | |||||
| N missing (% missing) | 193479 (0.564) | 1700911 (0.747) | 4413193 (0.813) | 14291 (0.158) | 6321874 (0.777) |
| Mean | 33.137 | 4.155 | 0.999 | 633.637 | 31.191 |
| SD | 173.836 | 22.383 | 51.42 | 4293.931 | 890.996 |
| Minimum | -51.596 | -21.111 | -4 | -173.11 | -173.11 |
| Median (IQR) | 21.475 (10.7 - 46.026) | 2.2 (1.259 - 4.262) | 0.365 (0.15 - 0.73) | 154.136 (88.344 - 328.826) | 0.9 (0.3 - 3.027) |
| Maximum | 52570 | 6242.75 | 40000 | 264085 | 264085 |
| Working Capital (EUR Mln.) | |||||
| N missing (% missing) | 107518 (0.314) | 1104205 (0.485) | 4479381 (0.825) | 11005 (0.122) | 5702109 (0.701) |
| Mean | 3.67 | 0.567 | 0.123 | 71.911 | 3.022 |
| SD | 44.127 | 4.743 | 1.538 | 599.013 | 109.879 |
| Minimum | -278 | -45.704 | -423 | -3038.101 | -3038.101 |
| Median (IQR) | 0.909 (0 - 4.21) | 0.095 (0 - 0.531) | 0.007 (0 - 0.09) | 14.698 (1.533 - 41.965) | 0.044 (0 - 0.402) |
| Maximum | 20306.179 | 3000.496 | 673 | 41938 | 41938 |
| Current Assets (EUR Mln.) | |||||
| N missing (% missing) | 19543 (0.057) | 203751 (0.09) | 3508347 (0.646) | 2990 (0.033) | 3734631 (0.459) |
| Mean | 10.575 | 1.702 | 0.421 | 279.997 | 7.327 |
| SD | 73.703 | 20.436 | 5.948 | 3508.954 | 496.892 |
| Minimum | 0 | -6.147 | -30.555 | -0.628 | -30.555 |
| Median (IQR) | 4.863 (1.738 - 12.47) | 0.839 (0.362 - 1.94) | 0.199 (0.05 - 0.48) | 48.59 (20.319 - 125.123) | 0.5 (0.159 - 1.433) |
| Maximum | 25472.361 | 13221.685 | 2818.342 | 302073 | 302073 |
| Total Assets (EUR Mln.) | |||||
| N missing (% missing) | 23454 (0.068) | 231118 (0.102) | 3525485 (0.649) | 4098 (0.045) | 3784155 (0.465) |
| Mean | 26.875 | 3.124 | 0.809 | 660.647 | 16.907 |
| SD | 117.127 | 25.891 | 85.557 | 6285.792 | 892.926 |
| Minimum | -24.489 | -9.88 | -2.215 | -3.055 | -24.489 |
| Median (IQR) | 16.01 (4.598 - 32.852) | 1.552 (0.647 - 3.635) | 0.325 (0.086 - 0.697) | 119.833 (45.466 - 314.089) | 0.816 (0.291 - 2.622) |
| Maximum | 38878.093 | 13221.685 | 110982.606 | 497114 | 497114 |
| Current Liabilities (EUR Mln.) | |||||
| N missing (% missing) | 19363 (0.056) | 211989 (0.093) | 3511461 (0.647) | 2796 (0.031) | 3745609 (0.46) |
| Mean | 5.467 | 0.733 | 0.214 | 165.764 | 4.151 |
| SD | 112.898 | 30.03 | 37.61 | 1865.356 | 268.354 |
| Minimum | -61019.547 | -4.744 | -0.728 | 0 | -61019.547 |
| Median (IQR) | 1.714 (0.099 - 5.824) | 0.112 (0 - 0.576) | 0.006 (0 - 0.109) | 23.723 (8.088 - 65.46) | 0.048 (0 - 0.409) |
| Maximum | 14871.1 | 40115.384 | 51087.048 | 141614 | 141614 |
| Current Loans (EUR Mln.) | |||||
| N missing (% missing) | 99305 (0.29) | 1064366 (0.468) | 4380980 (0.807) | 9578 (0.106) | 5554229 (0.682) |
| Mean | 1.063 | 0.113 | 0.025 | 28.947 | 1.07 |
| SD | 5.754 | 11.59 | 2.645 | 638.534 | 113.431 |
| Minimum | -0.305 | -4.288 | -0.189 | 0 | -4.288 |
| Median (IQR) | 0 (0 - 0.5) | 0 (0 - 0) | 0 (0 - 0) | 0.439 (0 - 7.327) | 0 (0 - 0) |
| Maximum | 1005.176 | 12700 | 2371.493 | 56184 | 56184 |
| Long-Term Debt (EUR Mln.) | |||||
| N missing (% missing) | 20183 (0.059) | 204328 (0.09) | 3546863 (0.653) | 3187 (0.035) | 3774561 (0.464) |
| Mean | 7.773 | 1.064 | 0.342 | 148.769 | 4.205 |
| SD | 120.28 | 31.592 | 92.62 | 1766.655 | 261.052 |
| Minimum | -2217.233 | -40075.269 | -51087.024 | -24759 | -51087.024 |
| Median (IQR) | 0.798 (0 - 6.238) | 0.212 (0.005 - 0.911) | 0.026 (0 - 0.177) | 4.724 (0 - 37.64) | 0.094 (0 - 0.561) |
| Maximum | 61100 | 12861.075 | 111719.787 | 142682 | 142682 |
| Total Shareholders Funds (EUR Mln.) | |||||
| N missing (% missing) | 23454 (0.068) | 231118 (0.102) | 3525482 (0.649) | 4098 (0.045) | 3784152 (0.465) |
| Mean | 26.873 | 3.124 | 0.809 | 660.616 | 16.906 |
| SD | 117.124 | 25.891 | 85.557 | 6285.783 | 892.924 |
| Minimum | -24.489 | -9.88 | -39.132 | -3.055 | -39.132 |
| Median (IQR) | 16.009 (4.598 - 32.85) | 1.552 (0.647 - 3.635) | 0.324 (0.086 - 0.697) | 119.833 (45.466 - 314.089) | 0.816 (0.291 - 2.622) |
| Maximum | 38878.093 | 13221.685 | 110982.606 | 497114 | 497114 |
| Total Shareholders Funds and Liabilities (EUR Mln.) | |||||
| N missing (% missing) | 23232 (0.068) | 229200 (0.101) | 3520623 (0.648) | 4064 (0.045) | 3777119 (0.464) |
| Mean | 11.643 | 1.101 | 0.2 | 244.644 | 6.308 |
| SD | 85.186 | 13.8 | 7.065 | 1809.455 | 258.298 |
| Minimum | -5617.419 | -879 | -888.562 | -7460.1 | -7460.1 |
| Median (IQR) | 3.798 (0.629 - 13.761) | 0.366 (0.089 - 1.166) | 0.063 (0.016 - 0.231) | 38.5 (9.85 - 122.419) | 0.196 (0.033 - 0.781) |
| Maximum | 34542.561 | 10251.128 | 2602.375 | 128783 | 128783 |
| Liquidity Ratio (%) | |||||
| N missing (% missing) | 94987 (0.277) | 1012177 (0.445) | 4457241 (0.821) | 11292 (0.125) | 5575697 (0.685) |
| Mean | 4.374 | 4.55 | 5.946 | 2.886 | 5.011 |
| SD | 10.513 | 10.349 | 12.388 | 6.991 | 11.128 |
| Minimum | 0 | 0 | 0 | 0 | 0 |
| Median (IQR) | 1.42 (0.71 - 3.19) | 1.46 (0.68 - 3.62) | 1.72 (0.73 - 4.92) | 1.32 (0.77 - 2.41) | 1.53 (0.7 - 3.95) |
| Maximum | 100 | 100 | 100 | 99.91 | 100 |
| Solvency Ratio (%) | |||||
| N missing (% missing) | 25940 (0.076) | 260862 (0.115) | 3620250 (0.667) | 4426 (0.049) | 3911478 (0.481) |
| Mean | 39.034 | 34.859 | 41.648 | 39.777 | 38.178 |
| SD | 31.351 | 31.963 | 39.368 | 27.845 | 35.36 |
| Minimum | -99.94 | -100 | -100 | -99.98 | -100 |
| Median (IQR) | 36.56 (14.96 - 61.83) | 32.4 (11.26 - 58.57) | 42.15 (12.82 - 74.83) | 37.62 (20 - 58.03) | 36.41 (12.28 - 65.42) |
| Maximum | 100 | 100 | 100 | 100 | 100 |
| Credit Period (days) | |||||
| N missing (% missing) | 216933 (0.633) | 1946284 (0.855) | 5284454 (0.973) | 17536 (0.194) | 7465207 (0.917) |
| Mean | 17.275 | 8.346 | 9.991 | 21.026 | 11.745 |
| SD | 34.653 | 30.196 | 40.62 | 32.784 | 34.12 |
| Minimum | 0 | 0 | 0 | 0 | 0 |
| Median (IQR) | 11 (3 - 21) | 0 (0 - 6) | 0 (0 - 3) | 16 (8 - 26) | 0 (0 - 14) |
| Maximum | 988 | 997 | 993 | 995 | 997 |
From the summary statistics we do see the survivorship bias for all firms in the sample. The closing date year is 2014 at the 25th percentile, which does not give us much hope to have information at the years before the credit shock. The most complete information comes from the number of employees, current assets, current liabilities, total assets, long-term debt, total equity and right-hand side of the balance sheet and solvency ratio. We try to isolate those variables for the entire period available for those firms restricted with the incorporation date and the bank relationships data.
| Variable | Firm Size | Overall, N = 4321049 | |||
|---|---|---|---|---|---|
| Large, N = 247110 | Medium, N = 1552278 | Small, N = 2450526 | Very Large, N = 71135 | ||
| Closing Date (Year) | |||||
| N missing (% missing) | 0 (0) | 0 (0) | 0 (0) | 0 (0) | 0 (0) |
| Mean | 2014.98 | 2014.943 | 2015.548 | 2015.01 | 2015.289 |
| SD | 3.186 | 3.286 | 3.613 | 3.191 | 3.481 |
| Minimum | 2001 | 1997 | 1980 | 1999 | 1980 |
| Median (IQR) | 2015 (2012 - 2018) | 2015 (2012 - 2018) | 2016 (2013 - 2018) | 2015 (2012 - 2018) | 2016 (2013 - 2018) |
| Maximum | 2022 | 2022 | 2022 | 2022 | 2022 |
| Number of Employees | |||||
| N missing (% missing) | 65055 (0.263) | 754612 (0.486) | 801733 (0.327) | 7733 (0.109) | 1629133 (0.377) |
| Mean | 173.487 | 34.361 | 5.693 | 2320.569 | 80.057 |
| SD | 217.487 | 110.847 | 286.623 | 15015.627 | 2343.143 |
| Minimum | 1 | 1 | 1 | 1 | 1 |
| Median (IQR) | 130 (43 - 230) | 23 (15 - 43) | 3 (1 - 6) | 541 (170 - 1334.75) | 6 (2 - 21) |
| Maximum | 30500 | 90335 | 161000 | 655722 | 655722 |
| Cost of Employees (EUR Mln.) | |||||
| N missing (% missing) | 114824 (0.465) | 1388467 (0.894) | 2410268 (0.984) | 11612 (0.163) | 3925171 (0.908) |
| Mean | 8.655 | 2.433 | 0.708 | 117.291 | 21.607 |
| SD | 65.319 | 2.185 | 17.924 | 881.833 | 346.424 |
| Minimum | -9.468 | -4.613 | -1.125 | -64.913 | -64.913 |
| Median (IQR) | 6.363 (3.278 - 10.934) | 1.929 (0.799 - 3.526) | 0.239 (0.113 - 0.411) | 28.142 (10.432 - 62.972) | 3.325 (0.989 - 8.274) |
| Maximum | 22660 | 75.643 | 1338 | 42914 | 42914 |
| Current Assets (EUR Mln.) | |||||
| N missing (% missing) | 15932 (0.064) | 123548 (0.08) | 1393407 (0.569) | 2381 (0.033) | 1535268 (0.355) |
| Mean | 11.253 | 1.821 | 0.456 | 311.754 | 9.735 |
| SD | 65.248 | 13.755 | 4.74 | 3938.531 | 620.976 |
| Minimum | 0 | -6.147 | -0.272 | 0 | -6.147 |
| Median (IQR) | 5.746 (2.234 - 13.627) | 0.97 (0.432 - 2.151) | 0.262 (0.089 - 0.541) | 50.748 (22.378 - 129.451) | 0.641 (0.242 - 1.823) |
| Maximum | 24066.329 | 10051.702 | 2204 | 302073 | 302073 |
| Total Assets (EUR Mln.) | |||||
| N missing (% missing) | 18873 (0.076) | 143466 (0.092) | 1404087 (0.573) | 3272 (0.046) | 1569698 (0.363) |
| Mean | 26.153 | 3.071 | 0.741 | 705.57 | 21.427 |
| SD | 93.015 | 21.027 | 21.694 | 7024.392 | 1109.066 |
| Minimum | -24.489 | -8.159 | -1.559 | -3.055 | -24.489 |
| Median (IQR) | 15.581 (5.006 - 31.818) | 1.654 (0.729 - 3.668) | 0.391 (0.145 - 0.755) | 118.756 (47.45 - 306.222) | 1.007 (0.396 - 3.116) |
| Maximum | 29795.11 | 11845.434 | 7638 | 497114 | 497114 |
| Current Liabilities (EUR Mln.) | |||||
| N missing (% missing) | 15829 (0.064) | 130426 (0.084) | 1396433 (0.57) | 2251 (0.032) | 1544939 (0.358) |
| Mean | 5.802 | 0.744 | 0.187 | 183.6 | 5.491 |
| SD | 38.794 | 34.4 | 9.135 | 2085.905 | 330.958 |
| Minimum | -6.379 | -4.744 | -0.728 | 0 | -6.379 |
| Median (IQR) | 2.106 (0.264 - 6.301) | 0.121 (0 - 0.616) | 0.012 (0 - 0.126) | 24.823 (9.465 - 66.901) | 0.071 (0 - 0.516) |
| Maximum | 14871.1 | 40115.384 | 7708.979 | 141614 | 141614 |
| loan | |||||
| N missing (% missing) | 66620 (0.27) | 720290 (0.464) | 1873532 (0.765) | 6547 (0.092) | 2666989 (0.617) |
| Mean | 1.114 | 0.11 | 0.016 | 32.303 | 1.444 |
| SD | 5.693 | 0.911 | 0.543 | 711.477 | 140.743 |
| Minimum | -0.118 | -4.288 | -0.004 | 0 | -4.288 |
| Median (IQR) | 0 (0 - 0.668) | 0 (0 - 0) | 0 (0 - 0) | 0.663 (0 - 7.73) | 0 (0 - 0) |
| Maximum | 1005.176 | 373.27 | 254.172 | 56184 | 56184 |
| Long-Term Debt (EUR Mln.) | |||||
| N missing (% missing) | 15731 (0.064) | 121879 (0.079) | 1404137 (0.573) | 2378 (0.033) | 1544125 (0.357) |
| Mean | 6.414 | 0.898 | 0.257 | 152.907 | 4.88 |
| SD | 30.554 | 35.369 | 10.907 | 1965.548 | 311.42 |
| Minimum | -2217.233 | -40075.269 | -7701.03 | -24759 | -40075.269 |
| Median (IQR) | 0.784 (0 - 5.019) | 0.222 (0.01 - 0.862) | 0.039 (0 - 0.196) | 4.894 (0 - 34.915) | 0.121 (0 - 0.619) |
| Maximum | 11781.267 | 8426.402 | 3721.862 | 142682 | 142682 |
| Total Shareholders Funds (EUR Mln.) | |||||
| N missing (% missing) | 18873 (0.076) | 143466 (0.092) | 1404085 (0.573) | 3272 (0.046) | 1569696 (0.363) |
| Mean | 26.153 | 3.071 | 0.741 | 705.53 | 21.426 |
| SD | 93.015 | 21.027 | 21.694 | 7024.382 | 1109.063 |
| Minimum | -24.489 | -8.159 | -39.132 | -3.055 | -39.132 |
| Median (IQR) | 15.58 (5.006 - 31.818) | 1.654 (0.729 - 3.668) | 0.391 (0.145 - 0.755) | 118.756 (47.45 - 306.222) | 1.007 (0.396 - 3.116) |
| Maximum | 29795.11 | 11845.434 | 7638 | 497114 | 497114 |
| Total Shareholders Funds and Liabilities (EUR Mln.) | |||||
| N missing (% missing) | 18708 (0.076) | 142270 (0.092) | 1401960 (0.572) | 3252 (0.046) | 1566190 (0.362) |
| Mean | 11.676 | 1.167 | 0.222 | 256.047 | 7.959 |
| SD | 67.082 | 11.701 | 7.909 | 1989.868 | 315.592 |
| Minimum | -978.925 | -879 | -227.534 | -7460.1 | -7460.1 |
| Median (IQR) | 4.498 (0.992 - 14.161) | 0.448 (0.126 - 1.313) | 0.093 (0.026 - 0.277) | 40.773 (11.571 - 120.581) | 0.28 (0.059 - 1.038) |
| Maximum | 29065.231 | 9184.535 | 1838 | 128783 | 128783 |
| Solvency Ratio (%) | |||||
| N missing (% missing) | 20240 (0.082) | 159931 (0.103) | 1450104 (0.592) | 3492 (0.049) | 1633767 (0.378) |
| Mean | 40.776 | 36.82 | 41.144 | 40.182 | 38.848 |
| SD | 29.203 | 30.863 | 38.03 | 26.237 | 33.552 |
| Minimum | -99.94 | -100 | -100 | -98.88 | -100 |
| Median (IQR) | 38.99 (18.81 - 62.29) | 35.25 (13.9 - 60.18) | 41.73 (14.19 - 71.93) | 38.38 (21.67 - 57.415) | 37.79 (14.62 - 64.36) |
| Maximum | 100 | 100 | 100 | 100 | 100 |
We compare the current firms financial information with the firms we were using in the previous sample in the draft, to see whether we can retrieve some financial data for those. The numbers are actually very similar.
| Variable | Firm Size | Overall, N = 4346076 | |||
|---|---|---|---|---|---|
| Large, N = 249766 | Medium, N = 1563759 | Small, N = 2460638 | Very Large, N = 71913 | ||
| Closing Date (Year) | |||||
| N missing (% missing) | 0 (0) | 0 (0) | 0 (0) | 0 (0) | 0 (0) |
| Mean | 2014.969 | 2014.927 | 2015.486 | 2015 | 2015.247 |
| SD | 3.185 | 3.284 | 3.606 | 3.191 | 3.474 |
| Minimum | 2001 | 1997 | 1980 | 1999 | 1980 |
| Median (IQR) | 2015 (2012 - 2018) | 2015 (2012 - 2018) | 2016 (2013 - 2018) | 2015 (2012 - 2018) | 2016 (2013 - 2018) |
| Maximum | 2022 | 2022 | 2022 | 2022 | 2022 |
| Number of Employees | |||||
| N missing (% missing) | 65985 (0.264) | 762028 (0.487) | 824413 (0.335) | 7842 (0.109) | 1660268 (0.382) |
| Mean | 173.082 | 34.376 | 5.761 | 2299.473 | 80.469 |
| SD | 216.98 | 110.636 | 287.702 | 14880.696 | 2337.034 |
| Minimum | 1 | 1 | 1 | 1 | 1 |
| Median (IQR) | 130 (43 - 229) | 23 (15 - 43) | 3 (1 - 6) | 539 (169 - 1330) | 7 (2 - 21) |
| Maximum | 30500 | 90335 | 161000 | 655722 | 655722 |
| Cost of Employees (EUR Mln.) | |||||
| N missing (% missing) | 115970 (0.464) | 1397883 (0.894) | 2418654 (0.983) | 11711 (0.163) | 3944218 (0.908) |
| Mean | 8.653 | 2.436 | 0.718 | 116.169 | 21.364 |
| SD | 64.954 | 2.197 | 17.556 | 874.512 | 342.928 |
| Minimum | -9.468 | -8.806 | -1.125 | -64.913 | -64.913 |
| Median (IQR) | 6.36 (3.281 - 10.944) | 1.928 (0.798 - 3.53) | 0.241 (0.113 - 0.418) | 28.005 (10.393 - 62.79) | 3.311 (0.977 - 8.241) |
| Maximum | 22660 | 75.643 | 1338 | 42914 | 42914 |
| Current Assets (EUR Mln.) | |||||
| N missing (% missing) | 15663 (0.063) | 120345 (0.077) | 1362854 (0.554) | 2356 (0.033) | 1501218 (0.345) |
| Mean | 11.375 | 1.823 | 0.464 | 309.156 | 9.599 |
| SD | 83.537 | 13.694 | 5.663 | 3915.113 | 614.579 |
| Minimum | 0 | -6.147 | -0.376 | 0 | -6.147 |
| Median (IQR) | 5.747 (2.229 - 13.638) | 0.97 (0.431 - 2.152) | 0.26 (0.086 - 0.54) | 50.646 (22.255 - 129.105) | 0.634 (0.237 - 1.806) |
| Maximum | 25472.361 | 10051.702 | 2818.342 | 302073 | 302073 |
| Total Assets (EUR Mln.) | |||||
| N missing (% missing) | 18651 (0.075) | 140476 (0.09) | 1373876 (0.558) | 3253 (0.045) | 1536256 (0.353) |
| Mean | 26.291 | 3.079 | 0.879 | 698.941 | 21.141 |
| SD | 106.595 | 20.874 | 108.758 | 6981.131 | 1099.168 |
| Minimum | -24.489 | -8.159 | -1.559 | -3.055 | -24.489 |
| Median (IQR) | 15.606 (5.01 - 31.868) | 1.656 (0.729 - 3.675) | 0.388 (0.141 - 0.755) | 118.28 (47.073 - 305.286) | 0.995 (0.39 - 3.09) |
| Maximum | 29795.11 | 11845.434 | 110982.606 | 497114 | 497114 |
| Current Liabilities (EUR Mln.) | |||||
| N missing (% missing) | 15564 (0.062) | 127260 (0.081) | 1365883 (0.555) | 2222 (0.031) | 1510929 (0.348) |
| Mean | 5.822 | 0.748 | 0.196 | 182.246 | 5.415 |
| SD | 38.588 | 34.228 | 9.275 | 2073.22 | 327.405 |
| Minimum | -6.379 | -4.744 | -0.728 | 0 | -6.379 |
| Median (IQR) | 2.104 (0.263 - 6.324) | 0.121 (0 - 0.617) | 0.011 (0 - 0.125) | 24.794 (9.405 - 66.558) | 0.069 (0 - 0.51) |
| Maximum | 14871.1 | 40115.384 | 7708.979 | 141614 | 141614 |
| loan | |||||
| N missing (% missing) | 67046 (0.268) | 722962 (0.462) | 1862165 (0.757) | 6597 (0.092) | 2658770 (0.612) |
| Mean | 1.109 | 0.111 | 0.024 | 32.104 | 1.427 |
| SD | 5.682 | 0.94 | 3.153 | 707.517 | 139.366 |
| Minimum | -0.118 | -4.288 | -0.004 | 0 | -4.288 |
| Median (IQR) | 0 (0 - 0.658) | 0 (0 - 0) | 0 (0 - 0) | 0.642 (0 - 7.686) | 0 (0 - 0) |
| Maximum | 1005.176 | 373.27 | 2371.493 | 56184 | 56184 |
| Long-Term Debt (EUR Mln.) | |||||
| N missing (% missing) | 15495 (0.062) | 118613 (0.076) | 1374587 (0.559) | 2358 (0.033) | 1511053 (0.348) |
| Mean | 6.49 | 0.901 | 0.388 | 151.015 | 4.849 |
| SD | 54.426 | 35.109 | 107.882 | 1952.919 | 315.343 |
| Minimum | -2217.233 | -40075.269 | -7701.03 | -24759 | -40075.269 |
| Median (IQR) | 0.772 (0 - 5) | 0.222 (0.01 - 0.864) | 0.039 (0 - 0.197) | 4.773 (0 - 34.5) | 0.119 (0 - 0.614) |
| Maximum | 21859.46 | 8426.402 | 111719.787 | 142682 | 142682 |
| Total Shareholders Funds (EUR Mln.) | |||||
| N missing (% missing) | 18651 (0.075) | 140476 (0.09) | 1373874 (0.558) | 3253 (0.045) | 1536254 (0.353) |
| Mean | 26.29 | 3.079 | 0.879 | 698.901 | 21.14 |
| SD | 106.595 | 20.874 | 108.758 | 6981.121 | 1099.165 |
| Minimum | -24.489 | -8.159 | -39.132 | -3.055 | -39.132 |
| Median (IQR) | 15.604 (5.009 - 31.867) | 1.655 (0.729 - 3.675) | 0.388 (0.141 - 0.755) | 118.28 (47.073 - 305.286) | 0.995 (0.39 - 3.09) |
| Maximum | 29795.11 | 11845.434 | 110982.606 | 497114 | 497114 |
| Total Shareholders Funds and Liabilities (EUR Mln.) | |||||
| N missing (% missing) | 18485 (0.074) | 139242 (0.089) | 1371620 (0.557) | 3229 (0.045) | 1532576 (0.353) |
| Mean | 11.714 | 1.168 | 0.22 | 254.255 | 7.847 |
| SD | 66.84 | 11.63 | 7.839 | 1978.637 | 312.348 |
| Minimum | -978.925 | -706.907 | -888.562 | -7460.1 | -7460.1 |
| Median (IQR) | 4.492 (0.99 - 14.175) | 0.447 (0.125 - 1.313) | 0.091 (0.025 - 0.274) | 40.382 (11.414 - 120.207) | 0.275 (0.057 - 1.026) |
| Maximum | 29065.231 | 9184.535 | 1838 | 128783 | 128783 |
| Solvency Ratio (%) | |||||
| N missing (% missing) | 20038 (0.08) | 157372 (0.101) | 1423645 (0.579) | 3480 (0.048) | 1604535 (0.369) |
| Mean | 40.775 | 36.769 | 41.037 | 40.132 | 38.803 |
| SD | 29.254 | 30.911 | 38.221 | 26.282 | 33.705 |
| Minimum | -99.94 | -100 | -100 | -98.88 | -100 |
| Median (IQR) | 38.97 (18.72 - 62.323) | 35.19 (13.83 - 60.17) | 41.63 (13.95 - 72.06) | 38.32 (21.58 - 57.38) | 37.75 (14.49 - 64.45) |
| Maximum | 100 | 100 | 100 | 100 | 100 |
For those firms and those variables, we would like to understand how many we have for the years before the credit shock, from 2000 to 2008 as reference years. From the table below, we see that information of number and cost of employees is very limited, whereas information on current assets, current liabilities and long-term debt is more available. For weighting using financials data, it makes sense to use alternatively the three years before the shock, which are 2006, 2007, and 2008, with more emphasis on 2007 as we have more information than 2006 and it is safe enough to not be affected by the credit shock than 2008, net of the delay in reporting of Amadeus.
| Closing Date (Year) | Non-Missing Values | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| Number of Employees | Cost of Employees | Current Assets | Total Assets | Current Liabilities | Long-Term Debt | Total Equity | Total Equity and Liabilities | Solvency Ratio | |
| 2000 | 8 | 11 | 14 | 14 | 14 | 13 | 14 | 14 | 14 |
| 2001 | 14 | 30 | 38 | 38 | 38 | 38 | 38 | 38 | 38 |
| 2002 | 23 | 60 | 90 | 90 | 90 | 89 | 90 | 90 | 86 |
| 2003 | 24 | 157 | 256 | 256 | 256 | 254 | 256 | 257 | 249 |
| 2004 | 42 | 327 | 719 | 719 | 719 | 714 | 719 | 719 | 688 |
| 2005 | 199 | 627 | 8615 | 8616 | 8615 | 8435 | 8616 | 8621 | 8130 |
| 2006 | 1439 | 1003 | 20630 | 20630 | 20631 | 20117 | 20630 | 20649 | 19487 |
| 2007 | 2663 | 1782 | 30971 | 30973 | 30985 | 30405 | 30973 | 31005 | 29362 |
| 2008 | 4881 | 4687 | 46717 | 46717 | 46733 | 45896 | 46717 | 46754 | 44421 |
| Variable | 2006 | 2007 | 2008 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Large, N = 476 | Medium, N = 4599 | Small, N = 16806 | Very Large, N = 139 | Overall, N = 22020 | Large, N = 902 | Medium, N = 7766 | Small, N = 23857 | Very Large, N = 277 | Overall, N = 32802 | Large, N = 1540 | Medium, N = 14403 | Small, N = 32556 | Very Large, N = 434 | Overall, N = 48933 | |
| Number of Employees | |||||||||||||||
| N missing (% missing) | 419 (0.88) | 4312 (0.938) | 15755 (0.937) | 95 (0.683) | 20581 (0.935) | 770 (0.854) | 7104 (0.915) | 22064 (0.925) | 201 (0.726) | 30139 (0.919) | 1251 (0.812) | 12823 (0.89) | 29692 (0.912) | 286 (0.659) | 44052 (0.9) |
| Mean | 125.632 | 21.08 | 9.925 | 1883.364 | 74.017 | 197.992 | 27.715 | 10.713 | 1544.579 | 67.998 | 156.685 | 34.282 | 9.424 | 1151.176 | 60.809 |
| SD | 248.558 | 37.935 | 58.999 | 4552.991 | 853.782 | 432.489 | 50.27 | 54.594 | 3881.245 | 708.489 | 350.204 | 117.475 | 45.581 | 3095.102 | 582.982 |
| Minimum | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 1 | 1 | 1 |
| Median (IQR) | 46 (5 - 159) | 7 (3 - 23) | 4 (2 - 7) | 697 (122.25 - 2177.25) | 5 (2 - 10) | 123 (18.5 - 244.25) | 11 (4 - 33) | 4 (2 - 7) | 544 (118 - 1470.5) | 5 (2 - 13.5) | 76 (16 - 185) | 18 (6 - 41) | 4 (2 - 8) | 420.5 (39 - 938.5) | 6 (2 - 20) |
| Maximum | 1753 | 317 | 1754 | 29774 | 29774 | 4419 | 822 | 1635 | 30791 | 30791 | 4888 | 4354 | 1507 | 31971 | 31971 |
| Cost of Employees (EUR Mln.) | |||||||||||||||
| N missing (% missing) | 411 (0.863) | 4341 (0.944) | 16177 (0.963) | 88 (0.633) | 21017 (0.954) | 753 (0.835) | 7037 (0.906) | 23056 (0.966) | 174 (0.628) | 31020 (0.946) | 1180 (0.766) | 11888 (0.825) | 30925 (0.95) | 253 (0.583) | 44246 (0.904) |
| Mean | 7.37 | 1.325 | 0.45 | 83.268 | 5.335 | 7.609 | 1.292 | 0.558 | 63.638 | 5.094 | 5.826 | 1.146 | 0.445 | 47.97 | 3.07 |
| SD | 13.495 | 3.804 | 4.65 | 255.816 | 60.191 | 15.019 | 2.521 | 4.401 | 197.253 | 49.722 | 11.694 | 1.335 | 3.033 | 160.202 | 32.914 |
| Minimum | 0.009 | 0.002 | 0 | 0.001 | 0 | 0.006 | 0 | 0 | 0.016 | 0 | 0.002 | 0 | 0 | 0.011 | 0 |
| Median (IQR) | 4.381 (0.727 - 8.926) | 0.479 (0.13 - 1.46) | 0.138 (0.055 - 0.249) | 20.231 (2.968 - 62.436) | 0.195 (0.073 - 0.598) | 4.551 (0.792 - 8.783) | 0.816 (0.329 - 1.556) | 0.159 (0.062 - 0.291) | 13.396 (2.247 - 51.666) | 0.357 (0.128 - 1.3) | 2.334 (0.7 - 6.456) | 0.8 (0.395 - 1.494) | 0.184 (0.091 - 0.328) | 13.09 (1.806 - 38.033) | 0.506 (0.186 - 1.317) |
| Maximum | 102.381 | 55.485 | 115.551 | 1748.1 | 1748.1 | 132.706 | 51.402 | 111.263 | 1843.8 | 1843.8 | 135.239 | 23.173 | 106.58 | 1940.8 | 1940.8 |
| Current Assets (EUR Mln.) | |||||||||||||||
| N missing (% missing) | 2 (0.004) | 29 (0.006) | 1359 (0.081) | 0 (0) | 1390 (0.063) | 5 (0.006) | 38 (0.005) | 1788 (0.075) | 0 (0) | 1831 (0.056) | 12 (0.008) | 54 (0.004) | 2150 (0.066) | 0 (0) | 2216 (0.045) |
| Mean | 10.115 | 0.756 | 0.268 | 198.09 | 1.935 | 34.177 | 0.979 | 0.304 | 299.321 | 4.128 | 7.923 | 1.101 | 0.3 | 176.236 | 2.43 |
| SD | 83.073 | 3.108 | 3.177 | 1205.725 | 100.778 | 803.685 | 4.353 | 3.902 | 3387.008 | 348.936 | 50.111 | 4.84 | 2.219 | 1695.91 | 164.425 |
| Minimum | 0 | 0 | -0.017 | 0 | -0.017 | 0 | -0.061 | -0.008 | 0 | -0.061 | 0 | -0.057 | -0.001 | 0 | -0.057 |
| Median (IQR) | 0.568 (0.054 - 4.343) | 0.144 (0.05 - 0.437) | 0.102 (0.037 - 0.216) | 11.599 (0.317 - 57.267) | 0.111 (0.04 - 0.253) | 1.163 (0.124 - 5.965) | 0.204 (0.071 - 0.794) | 0.111 (0.041 - 0.239) | 10.083 (0.539 - 62.023) | 0.131 (0.047 - 0.322) | 1.599 (0.234 - 6.092) | 0.343 (0.109 - 1.13) | 0.122 (0.045 - 0.265) | 12.506 (0.867 - 57.089) | 0.164 (0.059 - 0.449) |
| Maximum | 1728.986 | 129 | 271.747 | 13689.7 | 13689.7 | 24066.329 | 244.609 | 478.747 | 56187.928 | 56187.928 | 1765.595 | 496.277 | 219.957 | 34711.51 | 34711.51 |
| Current Liabilities (EUR Mln.) | |||||||||||||||
| N missing (% missing) | 2 (0.004) | 29 (0.006) | 1358 (0.081) | 0 (0) | 1389 (0.063) | 6 (0.007) | 37 (0.005) | 1774 (0.074) | 0 (0) | 1817 (0.055) | 12 (0.008) | 48 (0.003) | 2140 (0.066) | 0 (0) | 2200 (0.045) |
| Mean | 2.275 | 0.398 | 0.111 | 78.861 | 0.755 | 3.399 | 5.643 | 0.14 | 151.166 | 2.957 | 3.11 | 0.477 | 0.13 | 106.121 | 1.318 |
| SD | 11.437 | 2.857 | 1.097 | 566.573 | 46.845 | 17.865 | 456.305 | 2.406 | 1731.014 | 280.787 | 14.801 | 2.361 | 1.307 | 1212.698 | 117.216 |
| Minimum | 0 | 0 | -0.053 | 0 | -0.053 | 0 | -0.035 | -0.032 | 0 | -0.035 | 0 | -0.02 | -0.013 | 0 | -0.02 |
| Median (IQR) | 0.003 (0 - 0.728) | 0.013 (0 - 0.138) | 0.005 (0 - 0.069) | 0.336 (0 - 19.348) | 0.006 (0 - 0.08) | 0.001 (0 - 1.097) | 0.011 (0 - 0.166) | 0.003 (0 - 0.067) | 0.052 (0 - 17.08) | 0.004 (0 - 0.085) | 0.016 (0 - 1.475) | 0.03 (0 - 0.285) | 0.005 (0 - 0.074) | 0.58 (0 - 19.477) | 0.01 (0 - 0.113) |
| Maximum | 203.353 | 148.916 | 72.869 | 6509.935 | 6509.935 | 329.933 | 40115.384 | 255.965 | 28708.723 | 40115.384 | 328.14 | 187.043 | 130.056 | 25069.117 | 25069.117 |
| Total Assets (EUR Mln.) | |||||||||||||||
| N missing (% missing) | 2 (0.004) | 29 (0.006) | 1359 (0.081) | 0 (0) | 1390 (0.063) | 5 (0.006) | 38 (0.005) | 1786 (0.075) | 0 (0) | 1829 (0.056) | 12 (0.008) | 54 (0.004) | 2150 (0.066) | 0 (0) | 2216 (0.045) |
| Mean | 23.749 | 2.352 | 0.567 | 412.782 | 4.273 | 57.073 | 2.252 | 0.627 | 507.086 | 7.196 | 22.953 | 2.323 | 0.596 | 405.101 | 5.616 |
| SD | 90.928 | 30.684 | 15.266 | 1713.838 | 146.171 | 995.264 | 7.822 | 12.399 | 3554.525 | 379.098 | 63.32 | 10.411 | 9.995 | 2000.966 | 197.11 |
| Minimum | 0 | -0.128 | -0.017 | 0.012 | -0.128 | 0 | -0.082 | -0.001 | 0.012 | -0.082 | 0 | -0.078 | -0.005 | 0.01 | -0.078 |
| Median (IQR) | 5.123 (0.353 - 21.858) | 0.289 (0.097 - 1.723) | 0.154 (0.059 - 0.32) | 46.927 (4.584 - 165.029) | 0.176 (0.068 - 0.426) | 7.235 (0.733 - 24.351) | 0.447 (0.135 - 2.274) | 0.168 (0.065 - 0.357) | 54.938 (7.431 - 168.966) | 0.207 (0.079 - 0.581) | 6.376 (1.039 - 23.89) | 0.75 (0.198 - 2.528) | 0.187 (0.073 - 0.396) | 57.411 (8.812 - 199.495) | 0.26 (0.1 - 0.845) |
| Maximum | 1728.998 | 2040 | 1787.747 | 13689.7 | 13689.7 | 29795.11 | 337.545 | 1577.576 | 56187.928 | 56187.928 | 1765.602 | 991.709 | 1537.036 | 34711.51 | 34711.51 |
| Long-Term Debt (EUR Mln.) | |||||||||||||||
| N missing (% missing) | 19 (0.04) | 124 (0.027) | 1755 (0.104) | 5 (0.036) | 1903 (0.086) | 21 (0.023) | 125 (0.016) | 2247 (0.094) | 4 (0.014) | 2397 (0.073) | 37 (0.024) | 189 (0.013) | 2804 (0.086) | 7 (0.016) | 3037 (0.062) |
| Mean | 4.548 | 1.321 | 0.248 | 89.813 | 1.181 | 6.606 | -4.393 | 0.247 | 145.056 | 0.565 | 6.299 | 0.944 | 0.273 | 79.298 | 1.413 |
| SD | 16.952 | 43.604 | 4.513 | 626.56 | 55.619 | 31.386 | 458.479 | 3.801 | 1677.607 | 279.699 | 19.458 | 5.013 | 3.731 | 548.111 | 53.627 |
| Minimum | 0 | -0.001 | -61.208 | 0 | -61.208 | 0 | -40075.269 | -143.231 | 0 | -40075.269 | 0 | -21.88 | -46.664 | 0 | -46.664 |
| Median (IQR) | 0.106 (0 - 2.231) | 0.042 (0 - 0.317) | 0.02 (0 - 0.107) | 0.263 (0 - 11.745) | 0.024 (0 - 0.138) | 0.226 (0 - 3.1) | 0.083 (0 - 0.542) | 0.026 (0 - 0.123) | 1.024 (0 - 15.083) | 0.035 (0 - 0.182) | 0.449 (0 - 3.973) | 0.129 (0.003 - 0.717) | 0.029 (0 - 0.136) | 1.712 (0 - 20.466) | 0.048 (0 - 0.25) |
| Maximum | 270.46 | 2913 | 400.975 | 7179.716 | 7179.716 | 714.779 | 91.824 | 399.425 | 27479.154 | 27479.154 | 328.626 | 404.128 | 398.735 | 9642.27 | 9642.27 |
Weights using the Financial Information
From the financial data we have, we take into account two dimensions of the firm within the industry clusters at county level:
the sensitivity of a firm to liquidity constraints generated by a credit shock, which give us how the firm compared to the county-level peers would be more exposed to a short-term credit shock, and
the county-level firm relevance, which gives us how important is the firm for the county’s economy compared to the county-level peers.
For the first measure, we consider the current ratio, which is current assets over current liabilities. It is a type of liquidity ratio that measures a company’s ability to pay off its current liabilities with its total current assets such as cash, accounts receivable and inventories. In case of a credit shock, the firms needs to rely on liquidity to pay off current liabilities, such as the salaries, and the higher the current ratio, the better is the company’s liquidity position.
For the second measure, we can either consider the number of employees or the cost of employees, which is also indicative of the size of the firm in a non-categorical fashion. From the summary table of financials for the subsample, we see that current assets and liabilities can be negative or zero, as well as cost of employees.
It is reasonable to think that the sensitivity of a firm depends on its size and industry. Firms within an industry might respond differently to credit shocks than industries in a different industry. County-level industry composition share of firm size should be absorbed by fixed effects in the identification, but we can still compare firms within the county by their current ratio.
We take the inverse rank of firms within a county by their current ratio and divide this rank by the sum of total values of ranks at county level, such that the weights sum up to one. Ranking takes care of the negative current assets and liabilities, as well as infinite values due to zero liabilities. Identical current ratios are weighted equally. The smaller the current ratio of a firm, the higher the rank and the bigger the weight assigned at county level.
We use the number or the cost of employees at firm level to weight for the size of a firm within a county. We combine the current ratio weights and the employees weights computing two different weighted averages for each weight and then divide by 2. The following table gives a summary of the weights with the financial information at firm level.
| Within-County Weights | Missing Values | Complete Rate (%) | Mean | Std. Dev. | Min. Value | 25th | Median | 75th | Max. Value | Histogram | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| rank(Current Ratio) | 2006 | 1511 | 0.9314 | 0.0195 | 0.0243 | 0.0004 | 0.0044 | 0.0118 | 0.0255 | 0.5000 | ▇▁▁▁▁ |
| 2007 | 1968 | 0.9400 | 0.0129 | 0.0162 | 0.0003 | 0.0030 | 0.0079 | 0.0169 | 0.5000 | ▇▁▁▁▁ | |
| 2008 | 2372 | 0.9515 | 0.0086 | 0.0106 | 0.0002 | 0.0020 | 0.0052 | 0.0111 | 0.3333 | ▇▁▁▁▁ | |
| No. Employees | 2006 | 20581 | 0.0653 | 0.2210 | 0.3080 | 0.0001 | 0.0120 | 0.0667 | 0.2929 | 1.0000 | ▇▁▁▁▁ |
| 2007 | 30139 | 0.0812 | 0.1382 | 0.2453 | 0.0000 | 0.0040 | 0.0250 | 0.1300 | 1.0000 | ▇▁▁▁▁ | |
| 2008 | 44052 | 0.0997 | 0.0795 | 0.1715 | 0.0000 | 0.0023 | 0.0123 | 0.0616 | 1.0000 | ▇▁▁▁▁ | |
| Payrolls | 2006 | 21017 | 0.0455 | 0.2981 | 0.3745 | 0.0000 | 0.0093 | 0.0804 | 0.5543 | 1.0000 | ▇▁▁▁▂ |
| 2007 | 31020 | 0.0543 | 0.1975 | 0.3016 | 0.0000 | 0.0072 | 0.0443 | 0.2400 | 1.0000 | ▇▁▁▁▁ | |
| 2008 | 44246 | 0.0958 | 0.0830 | 0.1691 | 0.0000 | 0.0045 | 0.0185 | 0.0715 | 1.0000 | ▇▁▁▁▁ | |
We perform the same operation on the sample of firms retrieved in the previous draft, to see whether we have similar weights on summaries, and we keep the option to use the previous data.
| Within-County Weights | Missing Values | Complete Rate (%) | Mean | Std. Dev. | Min. Value | 25th | Median | 75th | Max. Value | Histogram | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| rank(Current Ratio) | 2006 | 1372 | 0.9388 | 0.0191 | 0.0242 | 0.0004 | 0.0042 | 0.0114 | 0.0245 | 0.5000 | ▇▁▁▁▁ |
| 2007 | 1764 | 0.9480 | 0.0125 | 0.0157 | 0.0003 | 0.0029 | 0.0075 | 0.0161 | 0.5000 | ▇▁▁▁▁ | |
| 2008 | 2148 | 0.9578 | 0.0082 | 0.0103 | 0.0002 | 0.0019 | 0.0050 | 0.0105 | 0.3333 | ▇▁▁▁▁ | |
| No. Employees | 2006 | 20958 | 0.0648 | 0.2171 | 0.3070 | 0.0001 | 0.0096 | 0.0625 | 0.2810 | 1.0000 | ▇▁▁▁▁ |
| 2007 | 31216 | 0.0805 | 0.1342 | 0.2399 | 0.0000 | 0.0040 | 0.0235 | 0.1290 | 1.0000 | ▇▁▁▁▁ | |
| 2008 | 45833 | 0.0995 | 0.0772 | 0.1704 | 0.0000 | 0.0019 | 0.0116 | 0.0577 | 1.0000 | ▇▁▁▁▁ | |
| Payrolls | 2006 | 21358 | 0.0469 | 0.2902 | 0.3720 | 0.0000 | 0.0075 | 0.0732 | 0.5409 | 1.0000 | ▇▁▁▁▂ |
| 2007 | 32063 | 0.0556 | 0.1902 | 0.2952 | 0.0000 | 0.0064 | 0.0414 | 0.2261 | 1.0000 | ▇▁▁▁▁ | |
| 2008 | 46027 | 0.0956 | 0.0801 | 0.1654 | 0.0000 | 0.0041 | 0.0178 | 0.0684 | 1.0000 | ▇▁▁▁▁ | |
For the entire sample of firms, including those of which we do not have the financial information, we would like to consider the mean of the financial values of the firms in the same size category within their county for which we do have information, and assign it to all firms. However, it is possible that this information covers all firm size categories within a county, it can be that some of the firms in the sample with the bank relationships have no match. We pool together the all the years around 2006, from 2004 and 2008, and we take mean of the financial variables. We calculate the current ratio on the mean value, but we invert it considering 1/current ratio, such that the bigger the number obtained the more severe is the liquidity exposure.
| Within-County Weights | Missing Values | Complete Rate (%) | Mean | Std. Dev. | Min. Value | 25th | Median | 75th | Max. Value | Histogram |
|---|---|---|---|---|---|---|---|---|---|---|
| No. Employees | 20330 | 0.9633 | 0.0007 | 0.0020 | 0.0000 | 0.0001 | 0.0002 | 0.0008 | 0.1691 | ▇▁▁▁▁ |
| Payrolls | 20077 | 0.9637 | 0.0007 | 0.0021 | 0.0000 | 0.0001 | 0.0003 | 0.0007 | 0.1639 | ▇▁▁▁▁ |
| Current Ratio | 2608 | 0.9953 | 0.0007 | 0.0009 | 0.0000 | 0.0003 | 0.0005 | 0.0009 | 0.1082 | ▇▁▁▁▁ |
There is a significant lower level of missing values for firm weights, but we are not able to cover the full sample in any case.
It would be nice to repeat the same operation for the sample of firms retrieved in the previous draft, but I have not saved the firm size label for the firms thus I am unable to assign the weights to quite some of the firms, dropping down to a sample of 549781 firms when using the financials data to retrieve the size category for the firms.
Measuring County-Level Commerzbank Dependence
In this section, we calculate the measure of Commerzbank dependence at county level using the different weights at firm level we prepared in the previous section, comparing it to the equally weighted measure, the measures calculated from the previous sample of firms, and the one coming from Huber. The following histograms are the result of the computation of county-level Commerzbank dependence using different weighting or applying equal weights at firm level.
Exposure using Equal Weigthing
In the following histogram, we plot the distribution of the county-level Commerzbank dependence using the current firm sample with equal weights - just the arithmetic mean of firm-level Commerzbank dependence within a county - with the distributiuon of the county-level Commerzbank dependence using the previous sample we applied in the last draft. The mean of the distribution is smaller in the current sample (0.079 and 0.083), and we miss one county having less firms. Another important feature to notice for our design is that in the current firms’ sample we have one county with zero county-level Commerzbank dependence, Landau in der Pfalz (KKZ = 07313), and the missing county is Eisenach (KKZ = 16056). The county with zero Commerzbank dependence has two firms, with one bank relationship each and none of them is with Commmerzbank. Instead, in the previous sample we have 459 firms for this county, with an average Commerzbank dependence of 0.049. Overall, they follow a very similar distribution.
Figure 6: Distribution of the county-level Commerzbank dependence with equal weights (simple county-level average), compared to the measure in the previous version of the draft.
Exposure using Firm-Level Weights
In the following histograms, we compare the distribution of the county-level Commerzbank dependence using the individual level weights for different closing dates, from 2006 to 2008. Using the individual weights for the current ratio, the distribution is quite different along those years due to survivorship bias. Probably the most reliable source is 2007, following Berg et al. (2021). Comparing this distribution with the equally weighted distribution, the former is quite smoother than the latter but with higher distributional mean.
Figure 7: Distribution of the county-level Commerzbank dependence obtained using weights from the ranking of current ratio within the county, using firm-level financial data from 2006, 2007 or 2008.
Figure 8: Distribution of the county-level Commerzbank dependence obtained using weights from the ranking of current ratio from 2007 firm-level financial data within the county, compared to the equal weights.
We repeat the same exercise using individual weights constructed with number of employees and the cost of employees, and we see a very skewed distribution of the county-level Commerzbank dependence towards zero but with higher mean values. Very few firms have complete information about employees. Combining those weights with the weights using the ranking of current ratio does not change the shape of distribution by construction. Using the past data at firm level combined with the available firm level financials used for weighting does not change the distribution significantly.
Figure 9: Distribution of the county-level Commerzbank dependence obtained using weights from the number of employees within the county, using firm-level financial data from 2006, 2007 or 2008.
Figure 10: Distribution of the county-level Commerzbank dependence obtained using weights from the cost of employees within the county, using firm-level financial data from 2006, 2007 or 2008.
Exposure using weights from average values at firm size groups
In this section, we analyse the distribution of the county-level Commerzbank exposure obtained by weighting at firm-level within a county assigning the average value of each financial variable we are considering by firm size category at each firm of that firm size. Just to remember, the mean is taken by using the financials from 2004 to 2008 of those firms that are in the sample. Considering the weights for number or cost of employees against the equally weighted exposure, we obtain a smoother distribution and higher in mean, but we drop a few counties where we do not have financials to take the average for each type of firm size in the available data.
Figure 11: Distribution of the county-level Commerzbank dependence obtained using weights obtained from the average value at firm size level within a county of the total number of employees and the cost of employees, compared to the distribution using equal weights.
When combining the “size” weights obtained from number and cost of employees at firm size level with the inverse current ratio obtained as mean current liabilities over mean current assets by firm size within a county, we still have a smoother distribution than when using equal weights, but it skews the distribution compared to simply using the average cost of employees by firm size for weighting.
Figure 12: Distribution of the county-level Commerzbank dependence obtained using weights obtained from the average value at firm size level within a county of the total number of employees and the cost of employees combined with the current ratio created from mean total assets and mean total liabilities by firm size level within a county, compared to the distribution using equal weights.
The following graphs give an overview of the distribution of county-level Commerzbank dependence for the potentially most relevant and useful weighting techniques among those we have been trying to exploit.
Figure 13: Distribution of county-level Commerzbank dependence, measured using equal weights (first row), weights using the mean value of firm-level data of each firm size category pooling balance sheet data from 2004 to 2008 (second row), and combined weights with the inverse current ratio obtained as the average of current liabilities over the average current assets of each firm size category. The sample of firms is the newly obtained from Amadeus, except for the top right histogram, where the measure is computed using the firm sample from the previous draft.
Spatial Distribution of Commerzbank Dependence
We acquire the boundaries of the administrative areas (1:250 000) from the Federal Agency for Cartography and Geodesy of Germany (VG250). The dataset includes the administrative units of the hierarchical administrative levels from the country (state) down to the Gemeinden (municipalities) with administrative boundaries, key-numbers, names as well as designations. Lines are SingleLines in sf, whereas areas are MultiPolygons. Each area can comprise several single areas, such as regular area with exclaves or inset areas, each of these multipolygons corresponding to a dataset in the attribute table. The data are structured according to levels (country/state), Länder (federal states), Regierungsbezirke (administrative districts), Kreise (districts/counties), Verwaltungsgemeinschaften (administrative associations), Gemeinden (municipalities), whereby the areas contained are directly carrying the attributive information. Spatial reference is UTM projection in zone 32 or 33 Ellipsoid GRS80, Datum ETRS89 (EPSG:4258).
The dataset is divided into the different administrative levels of Germany:
- Staat (country) STA
- Länder (states) LAN
- Administrative districts RBZ
- Districts KRS (what we use)
- Administrative associations VWG
- Municipalities GEM
Also, in the dataset are comprised:
- Boundary lines LI, in each case the respective highest level is included.
- Points PK, each municipality has a point, representing the heart of the municipality.
Data are updated each year with the statuses in 31.12 and 01.01, and we are using the last updated version in September 2021. There are no major changes in the boundaries from 2016. Amtliche Gemeindeschlüssel (AGS) (Official Municipality Key) is the name of what they call KKZ in the SOEP and also present in the Kreise codes matching table, which is derived from the ARS through omission of the administrative association. The hierarchical structure of the administrative levels is represented by the Amtliche Regionalschlüssel (ARS) (territorial code). ARS and AGS constitute the keys of the products of the statistical offices of the Federal Government and of the Länder. Thus, the integration of statistical data and data synchronization, respectively, can easily be performed (cf. also DeStatis).
Details on the attributes:
ADEadministrative level, we want 4 = DistrictGFgeofactor, which can be 1 (waters without structures), 2 (waters with structures), 3 (land without structures), 4 (land with structure). The indication “waters” refers to the North and Baltic Seas as well as to Lake Constance. Each administrative unit has precisely one record entry with the GF value 4.ARSterritorial code: 1st-2nd digit for the Land, 3rd digit for the administrative district, 4th-5th digit for the district, 6th-9th digit administrative association, 10th-12th community identification number.AGSofficial municipality key: 1st-2nd digit is for the Land, 3rd digit for the administrative district, 4th-5th digit for the identification number of the district, and 6th-8th digit for the community identification number.GENGeographical NameBEZis the attribute for the district,GENgives the name, andNBDsays whether the attribute should be used for the full name.
We could have actually used this dataset for the identification of firms, but the other table provides us directly with the KKZ codes and not the AGS, which makes it more reliable. I still don’t understand why they are using different names for basically the same codes. For plotting the districts and assign the county values, it is possible to subtract from the shapefile VGS250_KRS everything that is not GF == 4, which is the land with structure, destring the AGS codes and create a new variable kkz_rek from that (they are 401).
Reading layer VG250_KRS' from data source/Users/dubidub/Documents/Projects/CreditPopulism/data/shapes/vg250/VG250_KRS.shp’
using driver `ESRI Shapefile’
Simple feature collection with 431 features and 23 fields
Geometry type: MULTIPOLYGON
Dimension: XY
Bounding box: xmin: 280371.1 ymin: 5235856 xmax: 921292.4 ymax: 6101487
Projected CRS: ETRS89 / UTM zone 32N
Here we plot the spatial distribution of the equally weighted county-level Commerzbank dependence using the current and the past firm sample and the same scale. We also plot the difference in the spatial distribution of the current sample exposure against the past sample. The most striking difference comes from the county that has zero dependence in the current sample, and probably the county for which we have no firms in the current sample. Overall, the current sample calculates smaller values of Commerzbank dependence except for some counties in the south of Germany.
Figure 14: Spatial Distribution of county-level Commerzbank dependence, measured using equal weights, from the current firm sample (left) and the past firm sample (right). The district highlighted in red has zero Commerzbank dependence in the current firm sample. The bins are deciles from the joint distribution of Commerzbank dependence.
Figure 15: Difference in Spatial Distribution of county-level Commerzbank dependence measured using equal weights, current firm sample against past firm sample.
We plot the spatial distribution of the county-level Commerzbank dependence calculated by weighting for the average number of employees and the average cost of employees by firm size category within a county. The re-weighting compared to the equal wights should re-balance the measure for firm heterogeneity in size at county level. The variation from equal weights is stronger for areas where bigger firms are more present, for example Ruhr Valley. The variance of the county exposure measure is much higher compared to equally weighting.
Figure 16: Spatial Distribution of county-level Commerzbank dependence measured using weights obtained from the average number of employees by firm size within a county, compared against equal weights.
Figure 17: Spatial Distribution of county-level Commerzbank dependence measured using weights obtained from the average cost of employees by firm size within a county, compared against equal weights.
We plot the spatial distribution of the county-level Commerzbank dependence calculated by weighting for the average number of employees and the average cost of employees by firm size category within a county. The re-weighting compared to the equal wights should re-balance the measure for firm heterogeneity in size at county level. The variation from equal weights is stronger for areas where bigger firms are more present, for example Ruhr Valley. The variance of the county exposure measure is much higher compared to equally weighting.
To conclude, we plot the spatial distribution of the county-level Commerzbank dependence calculated by combining the weighted average of firm-level Commerzbank dependence within a county with weights for the average number of employees (or average cost of employees) by firm size category within the county and the weighted average of firm-level Commerzbank dependence with weights for the inverse current ratio calculated from the average current assets and the average current liabilities by firm size category within the county. The re-weighting procedure should re-balance the importance of a firm within a county for its size and its exposure to a general liquidity shock in the business cycle. Final values are more balanced.
Figure 18: Spatial Distribution of county-level Commerzbank dependence measured using weights obtained from the average number of employees combined with weights obtained from the inverse current ratio from average current assets and average current liabilities by firm size within a county, compared against equal weights.
Figure 19: Spatial Distribution of county-level Commerzbank dependence measured using weights obtained from the average cost of employees combined with weights obtained from the inverse current ratio from average current assets and average current liabilities by firm size within a county, compared against equal weights.